Re: Lots of code. How many tables.

My question how to handle the codes has been answered. Use a table for each codetype.

To everybody in this thread, thanks for the time you have spend to give advise
to me, this is appreciated.
Thanks,

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

LEGACY,
not relational,
This system started in 1973.
(PDP-11 then to a VAX then to intel, own database, own OS, because of 7x24 hour reasons there has never been a conversion, the PDP-11 database was compatibel with the VAX the VAX database was compatibel with the intel. And by compatibel I mean forward and backwards on bitlevel).

>
> 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.
>
No, this chemical mixing is a hazard to the environment as wel as a hazard to who-ever is doing this.

> It mixes data and metadata

Yes it mixes data and metadata. (Not nice and should be avoided, I agree with that.).

> same datatype.

No, it does not force the same datatype, everything is stored in the same way (bytes) but this does not have to be the same datatype. (No strongtyping).

> code, so performance suffers.

The performance on a PDP-11 (with over 400 concurrend users) was good. (Better then any database (relational or not) could deliver at that time). (Using the OS from Digital only 64 users where supported and then you had no performance at all.)

> You have to load this whole table to use just one
If you read a row from a table, does the database load the whole table ? Same for the code, you only read the code which is needed. (Most accesses in our Legacy database cost less then the equivalent in a relational database. Access is customized to the use-cases in our domain. The database is less suetable for MIS/DSS/OLAP/Datamining etc. The legacy database has the known disadvantages of a legacy system).

> >> A simple code could be M for male and F for female. <<
>
> That is not the ISO sex code ..

ISO is not the only standard. The RIM uses M/F/U/O for gender. (RIM stands for reference information model, it is 'the' standard in health related 'design ?').

>
> >> Some codes have a long description, a short description, a mnemonic
> and sometimes some other fields [sic] new requirements will promt for
> new 'attributes'. <<
>
> 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.

If one models a "generic table", then there is a generic table. Oracle for example uses a generic model (6 basic tables) to hold all their 'meta'-information for the Oracle designer. (As far as I know). Some larger multinationals use a generic model (same 6 basic tables) to hold their 'indexes' on books/literature etc. (Wel at least one multinational does, but I understood that this was not the only one).

But by know I understand that there is a strong resentment to generic models, generic tables and generalisations in general.

Although I agree with most of what you advocate, I do not agree with the trashing anything which does not confirm to the rules you advocate. Comparing a used and proven method to a chemical hazard is uncalled for. Designing a new (and better) system does not make all previous systems obsolete and useless.

But I do appreciate that you are guiding me and others so that we do not make mistakes which can be prevented nowadays.

My question how to handle the codes has been answered. Use a table for each codetype.

to everybody in this thread, thanks for the time you have spend to give advise
to me, this is appreciated.
Thanks,
ben brugman Received on Tue Feb 17 2004 - 16:56:21 CET