Lots of code. How many tables.
Date: Tue, 10 Feb 2004 16:08:03 +0100
Message-ID: <4028f455$0$277$4d4ebb8e_at_read.news.nl.uu.net>
As in most organisations we use codes.
In our legacy system we had a dedicated
table to contain almost all codes. With an assortment of fields depending on the code type.
Now we are moving towards RDBMS we are
wondering what is a good strategie for codes.
We have lots of codes, there will be new codes and new requirements in the future.
A simple code could be M for male and F for female. Some codes have a long description, a short description,
a mnemonic and sometimes some other fields, new requirements will promt for new 'attributes'.Codes can be language specific, but can also be generic. Descriptions are language specific.
One table for all codes is not something we wish for. One table for each code(type) ? question mark for us. Using the generic (4/6 table) model for this. *) see below Combi : for important codes use a table for the other the generic solution ?
What are your thoughts about this?
Thanks for your time,
ben brugman
*)
The generic (4 / 6 table model) :
(Does anybody know where this generic model is described on the net ?)
four or six tables : for four tables this would be
objecttypes would hold the codetypes. (Example gender.)
objectattributestypes would hold the attributetypes (Example g_code,
g_description.)
objects would hold the 'existing' codes. (Example 'M', 'F')
values would hold the 'existing' values for the codes. (Example
'M','male','F','Female').
Haven't worked out the relationtype/relation tables. (Do not need them ???)
Offcourse (Constraints) :
An attributetype belongs to an objecttype.
An object is of an objecttype.
A value is of an attributetype.
A value belongs to an object.
The attribute table is a sort of EAV (Entity_attribute_value) table, but it can only hold existing attributes (from the attributetype table) on existing codes (in the objects table).)
The 2 not mentioned tables in this model are used for holding the relations between objects. One table describes relation types between objecttypes. The other table holds existing relations between existing objects. These two tables would not be used in a codes implementations with the generic model. Received on Tue Feb 10 2004 - 16:08:03 CET