Re: Lots of code. How many tables.

For declarative referential integrity, via foreign key constraints you would need these code tables too of course.

Tony wrote:

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

-- 
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands