Data Model: TypeCode Reference Tables

[C TO]

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.

[CBretana]

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]

Thanks. Wouldn't this create more repeated data? How would you compare
creating a parent table with creating trigger to enforce referential
integrity?

[--CELKO--]

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);

>> need to create a template that has a referential constraints check

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.

>> I thought about combining the Language and Book TypeCodes into one
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.

[CBretana]

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]

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.

[C TO]

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?

[C TO]

Correction: I meant "there was not any book or language..."

[C TO]

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--]

>> 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.

[--CELKO--]

Get a copy of my book SQL PROGRAMMING STYLE that just came out. I give
a list of postfixes with their definitions for use with an ISO-11179
naming convention. This is a simple two-part approach where the
postfix tells you something about the type of scale and measurement of
that attribute.

A code is enforced by an outside agency, like a government or standards
body. A type is well-understood in the context but not necessarily
enforcd by law or an external source. That is why you have a
"zip_code" and not a "zip_type", "zip_value" or whatever. Tho, I am
not opposed to saying simple "zip" if it makes sense in context of the
data model.

The really bad one you see all the time here is "foobar_type_id"
because a type is a scalar value on a nominal scale that can appear in
many different entities, while an identifier is unique to one and only
one entity in the data model. How can something be both? Hye why not
go all the way and have a "foobar_type_id_value"?

>> [ ..blindly assigning columns a VARCHAR(255)] What do you mean by
that? <<

Newbies use it a lot because it is a default in some code generators.
Do you really need CHAR(255) to describe a book_type? Look at the
size of the Dewey Decimal Classification and nothing is that long.
Length is a constraint we get for free in SQL. If you provide that
much space, it will be used one day and you will get a load of garbage;
what did you use for a CHECK() to see that only valid data can be put
there? probalby nothing. When I stress test a schema and they have an
NVARCHAR(255) column for something like a phone number, I load it with
a Buddhist sutra in Chinese.

[C TO]

Thank you very very much