Re: Lots of code. How many tables.

"ben brugman" <ben_at_niethier.nl> wrote in message news:<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.

In a relational database you should be highly concerned with the integrity of the data. This is best done declaratively, via check constraints and foreign key constraints. If you are validating against code tables or "EAV" tables, you won't have that option. Will you use database triggers instead, or just rely on your application's validation? If you don't enforce the integrity in the database, it is sure to become corrupted in no time.

By all means use a code table as an adjunct if it helps you build your user interface pick lists, etc. But not INSTEAD OF proper integrity constraints. Received on Wed Feb 11 2004 - 15:33:18 CET