Re: How to model Enumeration's in a Table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: --CELKO-- (remove.jcelko212_at_earthlink.net)
Date: 12/02/04 

Date: Thu, 02 Dec 2004 13:20:33 -0800

>> Now lets assume I go with the lookup table solution, lets say my
database has 100 tables (which is actually does) and of those 100 tables
at least half of them have at least 1 such status code .. <<

That is a bit unusal. In most RDBMSes, you expect to have a lot of
relationship tables which would not have too many descriptive codes.

I don't know your application, but a Japanese car company modeled its
American payroll system with about 50 tables. Someone with that complex
a data model is either a Fortune 100 company or they have a lot of
attribute splitting, sequential processing via tables used as scratch
tapes, etc.

I have an awful feeling that you may have some serious design problems,
no data dictionary and code that is built from a procedural mental model
of the world.

>> .. and many have even two or three different fields [sic] for
different types i.e. Order.Status.Cancelled and Order.Type.Internal /
External... So if I create
a table for every single one of these I could be adding another 100 or
more tables just for all those codes. <<

Unh? First of all, a column is totally different from a field; this
might be one of your problems. You expect to the same attributes to
appear in many locations -- that is, you have a customer_id that is
referenced (not copied like a field in a file system!) wherever you have
a customer; you do not have a special customer identifier for every
table.

Also, SQL does not use that naming convention you gave. Whree did it
come from? It looks like you meant to say:

CREATE TABLE OrderStatus
(ord_stat_code INTEGER NOT NULL PRIMARY KEY,
 ord_stat_dscrb CHAR(15) NOT NULL_;
INSERT INTO OrderStatusCodes VALUES (1, 'cancelled');
 ...

CREATE TABLE OrderTypes
(ord_type_code INTEGER NOT NULL PRIMARY KEY,
 ord_type_dscrb CHAR(15) NOT NULL_;
INSERT INTO OrderTypes VALUES (1, 'internal');
INSERT INTO OrderTypes VALUES (2, 'external');
 ...

Also enumeration codes are the weakest of all encoding designs. Bet you
did not think about the fact that you have to design encodings in an
RDBMS.

>> So, would you simply create One table and then allocate regions of
that table to various codes so for example OrderStatus's would get
100-199, PurchaseOrderStatus's would get 200-299 etc... I am not really
excited about either solution as I do not want over 200 Tables and the
management of a combined Table could also be a mess. <<

Look up the Zitzi Motor Company in one of Chris Date's old books for one
set of reason this stinks. Then Google on "One True Lookup Table"
(OTLT) or on MUCK tables. This design fallacy is constantly
re-discovered by Newbies.

Let me give ONE of about two dozen problems with an OTLT. I have an
OTLT with (n) different things crammed into in total violation of first
normal form. I have a join that uses (k < n) subsets. Case #1: The (k)
subsets are clustered all over the physical table space. I have to
bring in the whole table and page from one section of the OTLT to the
other very frequently; if the subsets are not clustered, I have to seek
constantly.

The properly designed code tables are often small enough (a few hundred
codes or less each) to fit into main storage and use no extra disk
seeking.

A table is a set; a set is made up of things of the same kind; SQL
engines expect that and are build to do joins. What would you name your
table, following ISO-11179 naming rules, of course? It would be
"status_or_type_or_zip_or ICDA_or_..." until you hit the limit of the
name space.

You have no data model.

>> Or is there yet a better solution ? <<

1) Stop thinking like a C programmer. The enumeration types are low
level constructs that have no place in SQL. Think in abstractions and
build a proper data model and Data Dictionary BEFORE you code one line
of SQL.

2) Look for an industry standard for any encoding. Yes, that is hard
work and requires research. God Bless Google.

3) If you cannot an industry standard encoding, then look for one from
another part of the company, a major supplier or client you can use. Do
not re-invent the wheel.

Ideally, your company wide data dictionary will have all of (1),(2) and
(3) for you to look up.

4) If you cannot an existing encoding, then Google some of my articles
on the design of encoding schemes or get a copy of DATA & DATABASES.
When you finish the scheme, do a walk-thru on it and finally put it in
your company wide data dictionary.

5) Hueristics:

If the code is well-known (no translation needed), stable and short,
then you can put it in a CHECK() constraint of a CREATE DOMAIN or CREATE
TABLE statement. Example: zip_code

If the code needs translation needed, is volitile and/or long, then you
can put it in a table and reference where it is needed. Example: Dewey
Decimal Classification.

--CELKO--
Please post DDL in a human-readable format and not a machne-generated
one. This way people do not have to guess what the keys, constraints,
Declarative Referential Integrity, datatypes, etc. in your schema are.
Sample data is also a good idea, along with clear specifications.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: SQL
    ... > If you design right, you can *shift* much behavior to being data and ... > SQL is close to being Turing Complete. ... It is a bad habit of yours. ... If you're seriously suggesting that CRUD applications are equal ...
    (comp.object)
  • Re: Object-oriented thinking in SQL context?
    ... away from arrays and other non-OO data structures associated ... SQL is constrained to 'trivial' arrays. ... Design: Logical Design", 4th edition. ... Something analogous happens with database design. ...
    (comp.databases.theory)
  • Re: Why does 1:3 relationsihp require another table?
    ... Pro SQL Server 2000 Database Design ... > there can be a relation with NULLs in its tuples (hence 1NF models with ... > can only conclude that you do not believe Louis and Lewis are correctly ...
    (microsoft.public.sqlserver.programming)
  • Re: Why does 1:3 relationsihp require another table?
    ... correct design for the new real-world model. ... there can be a relation with NULLs in its tuples (hence 1NF models with ... Here's a good argument for not letting me represent Louis and Lewis by ... SQL is an attempt to implement relational algebra ...
    (microsoft.public.sqlserver.programming)
  • Re: bypassing some of the parameters in a parameter query
    ... >> Well, WHERE does work in design view, but having a lot of ANDs and ORs ... what is simple and direct logic in SQL View ... >> query to design view and see if it makes sense to you. ... >> Tom Ellison ...
    (microsoft.public.access.queries)