Re: Lots of code. How many tables.
Date: Tue, 17 Feb 2004 07:09:11 -0600
Message-ID: <c0t3qa$6ld$1_at_news.netins.net>
"--CELKO--" <joe.celko_at_northface.edu> wrote in message
news:a264e7ea.0402161320.5abab11f_at_posting.google.com...
> >> In our legacy system we had a dedicated table to contain almost all
> codes. With an assortment of fields [sic] depending on the code type.
> <<
>
> That is insane. It mixes data and metadata, it means that an error in
> one code could trash values another code. It forces codes into the
> same datatype. You have to load this whole table to use just one
> code, so performance suffers. The queries have to be needlessly
> complex. Etc. Etc.
>
> This is the relational version of putting all your liquid chemicals
> into one tank.
Ah, but put your "the relational model is not the only good solution" hat on and get in synch with your inner filing system and you will find other models where it is considered good form to minimize the number of, uh, collections for the sake of simplicity. In such models, files (not "tables") can include child files. When looking at the plethora of validation and code-description data that goes into an application, it is very common and also considered by many to be good form to have a single file that looks like:
AbbreviationsFile
Abbrev_Type; e.g. "Gender"
Abbrev_Heading; e.g."Sex"
Abbrevs (multiple fields and cardinality of 1 or more) Abbrev; e.g. "M"
Abbrev_Desc; e.g. "Male"
This is so common that it would be considered a "design pattern" within the non-relational database world. It might be "insane" to attempt anything similar in an RDBMS, but it is a very logical implementation strategy in databases that permit, even encourage, such grouping of codes and descriptions.
>
> >> A simple code could be M for male and F for female. <<
>
> That is not the ISO sex code ..
>
But I suspect more common ;-)
> >> Some codes have a long description, a short description, a mnemonic
> and sometimes some other fields [sic] new requirements will promt for
> new 'attributes'. <<
Yes and you will often find one or more additional attributes in the multivalued group, whose meaning can be adjusted based on which type of abbreviation it is -- another relational no-no.
>
> Rows are not records; fields are not columns; tables are not files.
> There is no such thing as a "generic table" -- a table models a set; a
> set is made up of one and only specific kind of thing.
Bingo. So, if you are not living with the restrictions of an RDBMS, there is much more freedom to implement data along the lines of how one would think about it. If I have the option of paying for a software development team who must work within the relational rules of engagement, or one with the freedom of an XML-like structure, I'll choose the latter any day, being the stewardly person that I am. smiles. --dawn Received on Tue Feb 17 2004 - 14:09:11 CET