Re: Individual Type Code tables vs. one big one

Kevin Merritt <kevinmerritt_at_earthlink.net> wrote:

>Is there really a significant performance difference between keeping a bunch of small type code
>look up tables (ie 30 of them) vs. keeping just one big type code table with some sort of column
>that tells which type code it is? Any insight is appreciated.

IMHO, having worked on systems which use both approaches, I'm firmly on the side of a generic code table with a type column. The advantages are:

• One maintenance form and report for a generic code table vs a new maintenance form and report each time you create a new code type table (leading to a not inconsiderable saving in time to deliver new functionality - which is quicker: inserting a couple of rows in a table, or creating a new table, index, menu structure, maintenance report, etc?)
• Less tables, indexes, grants, synonyms, etc, to manage in the database
• Foreign key constraints can be implemented by using a composite key structure, eg, if your generic code table has two columns "TYPE" and "CODE", then your foreign table should also have two columns for the "TYPE" and "CODE". (Because the value of the "TYPE" column in the foreign table is probably going to be constant, you could use a row level table trigger to populate the column.) The only downside to having multiple tables all defined with foreign key constraints onto the same generic table is that deletes from the generic codes table may be a bit slower. But its the nature of code tables to be low in transaction volumes, with values fairly static, so this shouldn't be a real problem.
• By incorporating a "TYPE" and "CODE" column in you foreign table, and using a generic code table, your select queries that retrieve the code descriptions become identical, ie, select code_table.description from code_table, foreign_key_table where code_table.type = foreign_key_table.type and code_table.code = foreign_key_table.code You don't need to know which specific code table is related to which particular foreign key table (which, in my experience, can be a real pain if you have a large number of such tables)

If, after all that, you still want to see all your individual code types a seperate tables, you can always build "virtual" code tables with views (with the obvious limitations on inserts). But you may feel the use of views in select statements adds a necessary level of documentation to your statements that is hidden by using a generic codes table. Received on Fri Nov 15 1996 - 00:00:00 CET