Prev: Problem prefixing object with dbo. when using system stored procedures
Next: Querying ExecutionLog Table
From: JohnnyC on 15 Jul 2008 12:29
Hello,
As DBA's, do you have a preference to how code lookup tables are built? For
example, say I have the need for a number of lists of selectable values in
an application. The first is customer status, the second is salutation, etc
etc.

As a DBA, do you typically require a separate table for each list type,
something like
Create table [dbo].[Salutations] {
SalutationID int not null,
Salutation varchar(10) not null)

-or-

Do you find one table with a column indentifying the list type acceptable?
In this example, ListType might say "Salutation" etc
Create table [dbo].[LookupList](
ListID int not null,
ListType varchar(10) not null,
ListValue varchar(50) not null )

The list of each value type is probably no more than 20 or 30 entries. I
guess the real question is "Does it really matter which way these tables are
implemented?" I would like to follow generally accepted practices.

Thanks for your time.




From: --CELKO-- on 15 Jul 2008 13:04
>> As a DBA, do you typically require a separate table for each list type, something like

CREATE TABLE Salutations
(salutation_id INTEGER NOT NULL,
salutation VARCHAR(10) NOT NULL); <<

Of course. A table models a set of things of the same kind, but you
got this wrong. Why did you have no key and a magical, non-standard
"salutation_id"? It did not need to be 10 characters long. This
should be:

CREATE TABLE Salutations
(salutation_txt VARCHAR(5) NOT NULL PRIMARY KEY);

But with a list this short and stable, I would use a "CHECK
(salutation_txt IN ('Mr.', 'Ms.', ..))" instead. A better example
would be:

CREATE TABLE DeweyDecimalClassification
(ddc CHAR(7) NOT NULL PRIMARY KEY
CHECK (ddc LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'),
ddc_description VARCHAR(150) NOT NULL);

-or-

>> Do you find one table with a column identifying the list type acceptable? <<

NEVER!!! This nightmare is so bad it has a name --OTLT (One True
Lookup Table) or MUCK (Massively Unified Code Keys). Google it.

If you want the details with examples and a few thousand words, get a
copy of THINKING IN SETS and read the chapter on this damn thing.
Then read the chapters on the design of encoding schemes.
From: Plamen Ratchev on 15 Jul 2008 13:09
It does matter how the tables are implemented. The second design you
describe is known as one true lookup table and you cannot enforce data
integrity via constraints. Read the following article on the topic:
http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: Aaron Bertrand [SQL Server MVP] on 15 Jul 2008 13:12
> But with a list this short and stable, I would use a "CHECK
> (salutation_txt IN ('Mr.', 'Ms.', ..))" instead.

Until you have to put that check constraint on 20 different tables and/or in
multiple databases. And then someone says, oh we forgot 'Dr.'...

A

From: Michael Ware on 15 Jul 2008 13:37
Johnny,

--CELKO--'s right (to an extent; but never say never).

There are situations where a single table with multiple Code Lookup lists
can simplify development, but you need to be very cautious about choosing
that approach. The two biggest downsides are lack of flexibility, and
increased complexity in the table maintenance. The only advantage is the
ability to isolate some (but not all) of your field validation and lookup
code into a generic class. However, unless you are dealing with at least
dozens of code sets, the time savings in development will not out weigh the
drawbacks.


-Mike
----------------------
Vote early and vote often.
-Al Capone

"JohnnyC" <j(a)jc.com> wrote in message
news:Gf4fk.20596$%q.11957(a)newsfe24.lga...
> Hello,
> As DBA's, do you have a preference to how code lookup tables are built?
> For example, say I have the need for a number of lists of selectable
> values in an application. The first is customer status, the second is
> salutation, etc etc.
>
> As a DBA, do you typically require a separate table for each list type,
> something like
> Create table [dbo].[Salutations] {
> SalutationID int not null,
> Salutation varchar(10) not null)
>
> -or-
>
> Do you find one table with a column indentifying the list type acceptable?
> In this example, ListType might say "Salutation" etc
> Create table [dbo].[LookupList](
> ListID int not null,
> ListType varchar(10) not null,
> ListValue varchar(50) not null )
>
> The list of each value type is probably no more than 20 or 30 entries. I
> guess the real question is "Does it really matter which way these tables
> are implemented?" I would like to follow generally accepted practices.
>
> Thanks for your time.
>
>
>
>


 |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Problem prefixing object with dbo. when using system stored procedures
Next: Querying ExecutionLog Table