Data Model: TypeCode Reference Tables

Hello World,
I have two two typcode reference tables with the following DDL:
CREATE TABLE [dbo].[LanguageType](
[Description] [varchar](255) NULL,
[LanguageTypeCode][char](6)NOT NULL
)
GO
ALTER TABLE dbo.LanguageType ADD CONSTRAINT
PK_LanguageType PRIMARY KEY CLUSTERED
(
LanguageTypeCode
)
GO
CREATE TABLE [dbo].[BookType](
[Description] [varchar](255) NULL,
[BookTypeCode] [char](6)NOT NULL
)
GO
ALTER TABLE dbo.BookType ADD CONSTRAINT
PK_BookType PRIMARY KEY CLUSTERED
(
BookTypeCode
)
GO
I need to create a template that has a referential constraints check against
the primary key of EITHER table above. For example,
ALTER TABLE dbo.Template ADD CONSTRAINT
PK_Template PRIMARY KEY CLUSTERED
(
TemplateID,
TypeCode -- Either LanguageTypeCode or BookTypeCode
)
What are the options that I have to implement this? I thought about
combining the Language and Book TypeCodes into one table, but we think that
would only complicate our data model. Please comment.Add a "parent" Table that has both TypeCoeds in it
Create Table TemplateTypeCode -- You can think of better name for this
abstraction
(TypeCode Char(6( Primary Key Not Null)
And alter both pf your other tables
Alter Table dbo.LanguageType
ADD CONSTRAINT FKTypeCode Foreign Key (LanguageTypeCode)
references TemplateTypeCode(TypeCode)
Alter Table dbo.BookType
ADD CONSTRAINT FKTypeCode Foreign Key (BookTypeCode)
references TemplateTypeCode(TypeCode)
Then create constraint on Template table agaist TemplateTypeCode Table
ALTER TABLE dbo.Template ADD CONSTRAINT
FK_TypeCodes (TypeCode)
references TemplateTypeCode(TypeCode)
"C TO" wrote:

> Hello World,
> I have two two typcode reference tables with the following DDL:
> CREATE TABLE [dbo].[LanguageType](
> [Description] [varchar](255) NULL,
> [LanguageTypeCode][char](6)NOT NULL
> )
> GO
> ALTER TABLE dbo.LanguageType ADD CONSTRAINT
> PK_LanguageType PRIMARY KEY CLUSTERED
> (
> LanguageTypeCode
> )
> GO
> CREATE TABLE [dbo].[BookType](
> [Description] [varchar](255) NULL,
> [BookTypeCode] [char](6)NOT NULL
> )
> GO
> ALTER TABLE dbo.BookType ADD CONSTRAINT
> PK_BookType PRIMARY KEY CLUSTERED
> (
> BookTypeCode
> )
> GO
> I need to create a template that has a referential constraints check again
st
> the primary key of EITHER table above. For example,
> ALTER TABLE dbo.Template ADD CONSTRAINT
> PK_Template PRIMARY KEY CLUSTERED
> (
> TemplateID,
> TypeCode -- Either LanguageTypeCode or BookTypeCode
> )
>
> What are the options that I have to implement this? I thought about
> combining the Language and Book TypeCodes into one table, but we think tha
t
> would only complicate our data model. Please comment.|||Thanks. Wouldn't this create more repeated data? How would you compare
creating a parent table with creating trigger to enforce referential
integrity?
"CBretana" wrote:
> Add a "parent" Table that has both TypeCoeds in it
> Create Table TemplateTypeCode -- You can think of better name for this
> abstraction
> (TypeCode Char(6( Primary Key Not Null)
> And alter both pf your other tables
> Alter Table dbo.LanguageType
> ADD CONSTRAINT FKTypeCode Foreign Key (LanguageTypeCode)
> references TemplateTypeCode(TypeCode)
> Alter Table dbo.BookType
> ADD CONSTRAINT FKTypeCode Foreign Key (BookTypeCode)
> references TemplateTypeCode(TypeCode)
> Then create constraint on Template table agaist TemplateTypeCode Table
> ALTER TABLE dbo.Template ADD CONSTRAINT
> FK_TypeCodes (TypeCode)
> references TemplateTypeCode(TypeCode)
>
> "C TO" wrote:
>|||A thing is either a type or a code, but not both. You also might want
to consider not blindly assigning columns a VARCHAR(255).
CREATE TABLE Languages
(language_code CHAR(3) NOT NULL PRIMARY KEY, -- ISO Standared 3166
language_name CHAR(15) NOT NULL);
CREATE TABLE BookTypes
( book_type CHAR(6) NOT NULL PRIMARY KEY, -- ABA codes? shop
categories?
book_description VARCHAR(25) NOT NULL);
against the primary key of EITHER table above. For example, ...
TypeCode -- Either LanguageTypeCode or BookTypeCode <<
No, you need a real relational design with well-defined attributes. It
is a total violation of just about every rule to have a column that
holds different attributes. That is less than First Normal Form.
table, but we think that would only complicate our data model. Please
comment. <<
That design flaw is called the "One True Lookup Table" and you can
Goolge for the painful details.
Here are some standards that might help:
1) If you need to know the language of a book, you can get it from the
fist two digits of the ISBN.
2) ISO 3166 standard reference with 2 letter, 3 letter and number
codes:
The standard ISO 639 provides two sets of language codes, one as a
two-letter code set (ISO 639-1:2002 Codes for the representation of
names of languages -- Part 1: Alpha-2 code) and another as a
three-letter code set (ISO 639-2:1998 Codes for the representation of
names of languages -- Part 2: Alpha-3 code). The ISO 639 code lists are
also made available via the Web site of the ISO 639 Joint Advisory
Committee.|||CTO,
Creating a parent table is the way I'd go... You ARE creating repeated
data (copies) of the Keys, but that happens anytime you have a Foreign Key
to Primary Key Constraint. This technique is called "subclassing". It's
advantedge is that the FK On the Template table can use built in DRI
COnstraint which is better performance and cleaner than using Trigger...
"C TO" wrote:
> Thanks. Wouldn't this create more repeated data? How would you compare
> creating a parent table with creating trigger to enforce referential
> integrity?
>
> "CBretana" wrote:
>|||Hi CELKO,
Your input is really helpful and make me think harder. First of all, the
examples I provided related to Book and Language types were bad in this case
because language is an attribute of the book. But I really meant to provide
examples that would answer my question about "One True Lookup Table" and the
referential integrity enforment.
Secondly, I have heard you mentioned ISO before and felt very helpless
soemtimes because I did not how to get access to the codes. I've tried, many
of them required "placing an order in the shopping cart". BTW, in general,
how do I know which are accessible to the Public?
There were really book or language in this model, but I am interested in
using the universal, standard reference codes. Thanks.
"--CELKO--" wrote:

> A thing is either a type or a code, but not both. You also might want
> to consider not blindly assigning columns a VARCHAR(255).
> CREATE TABLE Languages
> (language_code CHAR(3) NOT NULL PRIMARY KEY, -- ISO Standared 3166
> language_name CHAR(15) NOT NULL);
> CREATE TABLE BookTypes
> ( book_type CHAR(6) NOT NULL PRIMARY KEY, -- ABA codes? shop
> categories?
> book_description VARCHAR(25) NOT NULL);
>
> against the primary key of EITHER table above. For example, ...
> TypeCode -- Either LanguageTypeCode or BookTypeCode <<
> No, you need a real relational design with well-defined attributes. It
> is a total violation of just about every rule to have a column that
> holds different attributes. That is less than First Normal Form.
>
> table, but we think that would only complicate our data model. Please
> comment. <<
> That design flaw is called the "One True Lookup Table" and you can
> Goolge for the painful details.
> Here are some standards that might help:
> 1) If you need to know the language of a book, you can get it from the
> fist two digits of the ISBN.
> 2) ISO 3166 standard reference with 2 letter, 3 letter and number
> codes:
> The standard ISO 639 provides two sets of language codes, one as a
> two-letter code set (ISO 639-1:2002 Codes for the representation of
> names of languages -- Part 1: Alpha-2 code) and another as a
> three-letter code set (ISO 639-2:1998 Codes for the representation of
> names of languages -- Part 2: Alpha-3 code). The ISO 639 code lists are
> also made available via the Web site of the ISO 639 Joint Advisory
> Committee.
>|||Thanks. If that is the only common practice, then I would go that route too.
Talking about DRI, is that still available in SQL 2005?
"CBretana" wrote:
> CTO,
> Creating a parent table is the way I'd go... You ARE creating repeated
> data (copies) of the Keys, but that happens anytime you have a Foreign Ke
y
> to Primary Key Constraint. This technique is called "subclassing". It's
> advantedge is that the FK On the Template table can use built in DRI
> COnstraint which is better performance and cleaner than using Trigger...
> "C TO" wrote:
>|||Correction: I meant "there was not any book or language..."
"C TO" wrote:
> Hi CELKO,
> Your input is really helpful and make me think harder. First of all, the
> examples I provided related to Book and Language types were bad in this ca
se
> because language is an attribute of the book. But I really meant to provid
e
> examples that would answer my question about "One True Lookup Table" and t
he
> referential integrity enforment.
> Secondly, I have heard you mentioned ISO before and felt very helpless
> soemtimes because I did not how to get access to the codes. I've tried, ma
ny
> of them required "placing an order in the shopping cart". BTW, in general,
> how do I know which are accessible to the Public?
> There were really book or language in this model, but I am interested in
> using the universal, standard reference codes. Thanks.
>
>
> "--CELKO--" wrote:
>|||Hi CELKO,
I overlooked a point you pointed out about "A thing is either a type or a
code". Do you mean in general a type is usually a popular thing which can be
understood easily in a spoken/written language (e.g. Type of Language:
English, Spanish...) and the code is usually used for application only (e.g
.
EN, SP) ? So you were questioning why the column was named that way?
You further said, "You also might want to consider not blindly assigning
columns a VARCHAR(255)." What do you mean by that?
Say, I have a type of a thing that is not so common or not to have easily
standardized type, having a description column would allow further
explaination about that type. Why not?
"--CELKO--" wrote:

> A thing is either a type or a code, but not both. You also might want
> to consider not blindly assigning columns a VARCHAR(255).
> CREATE TABLE Languages
> (language_code CHAR(3) NOT NULL PRIMARY KEY, -- ISO Standared 3166
> language_name CHAR(15) NOT NULL);
> CREATE TABLE BookTypes
> ( book_type CHAR(6) NOT NULL PRIMARY KEY, -- ABA codes? shop
> categories?
> book_description VARCHAR(25) NOT NULL);
>
> against the primary key of EITHER table above. For example, ...
> TypeCode -- Either LanguageTypeCode or BookTypeCode <<
> No, you need a real relational design with well-defined attributes. It
> is a total violation of just about every rule to have a column that
> holds different attributes. That is less than First Normal Form.
>
> table, but we think that would only complicate our data model. Please
> comment. <<
> That design flaw is called the "One True Lookup Table" and you can
> Goolge for the painful details.
> Here are some standards that might help:
> 1) If you need to know the language of a book, you can get it from the
> fist two digits of the ISBN.
> 2) ISO 3166 standard reference with 2 letter, 3 letter and number
> codes:
> The standard ISO 639 provides two sets of language codes, one as a
> two-letter code set (ISO 639-1:2002 Codes for the representation of
> names of languages -- Part 1: Alpha-2 code) and another as a
> three-letter code set (ISO 639-2:1998 Codes for the representation of
> names of languages -- Part 2: Alpha-3 code). The ISO 639 code lists are
> also made available via the Web site of the ISO 639 Joint Advisory
> Committee.
>|||>> I did not how to get access to the codes. I've tried, many of them
required "placing an order in the shopping cart". BTW, in general, how
do I know which are accessible to the Public? <<
ISO makes its money from sales, while ANSI makes its money from
membership fees. ISOP is therefore not as open about things as ANSI.
You have to Google around like crazy sometimes to find out about a
Standard if you do not know the ISO number or a good search phrase for
the title. I think that thre might be market for a pocket guidebook to
various standard codes -- the name, source, purpose and brief
history/description of each one.