Re: How to model Enumeration's in a Table

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

From: james (nospam_at_hypercon.net)
Date: 12/02/04 

Date: Thu, 2 Dec 2004 16:18:08 -0600

inline...

"--CELKO--" <remove.jcelko212@earthlink.net> wrote in message
news:uNjRDTL2EHA.1400@TK2MSFTNGP11.phx.gbl...
>>> 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.

    Payroll is one application, I have 30 applications all interrelated.

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

    SQL Column => OO Field i.e. TableEmployee.FirstName =>
EmployeeObject.FirstName
    Lets not nitpick this point and move on.

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

    Yes, these are exactly what I am referring to and here you have created
TWO Tables. My point is this, PurchaseOrder's have a status, Orders have a
status, Parts have a State(Active/Inactive), Customers have a
State(Prospect, Active, HoldForCredit, Inactive) I can go on and on. Are
you saying having status COLUMNS is bad design? Or are you telling me to
create 50 new tables? That was my point.

>
> 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.
>
    This is why I asked my question in the first place.

>>> 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.
>
    I was just throwing this out there as a question to the previous poster
NOT as a GOOD solution. I know it stinks. Your preaching to the choir
here.

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

    Thanks

JIM

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

  • ZIP Codes and barcodes [telecom]
    ... with another three digits which are the last two digits of your ... If a firm has a lot of incoming mail, it might get one or more ZIP+4 codes, ... but this has become less necessary with encoding to delivery point. ...
    (comp.dcom.telecom)
  • Re: Chinese buildings
    ... sometimes the Codes and Standard are not enough to produce a safe ... of course why the canons of engineering ethics demand public ... design was found not to be at fault. ...
    (sci.military.naval)
  • Re: Electric Lock choice
    ... > what Codes they follow at any given time. ... > designed to exit you onto a Tarmac, ... The design, (Older ... >> around an airport... ...
    (alt.security.alarms)
  • Re: Abbreviation List Tables Design, aka OTLT
    ... The sort of things that we decide don't need their own table include some ... > example, VENDOR-STATUS, no program is going to recognize those codes ... >> access system control items. ... It is almost always dismissed as a terrible design by ...
    (comp.databases.pick)