Re: composite foreign key one field constant?

ddwoske_at_yahoo.com (Don Dwoske) writes:

> There are differing opinions on this point, and at the moment, I just
> don't see how it could create complicated and buggy sql down the road.
>

I find the approach of using a single code table to store multiple types of codes has a number of problems -

1. Foreign Key constraints which use the code table become more complex as you need to provide both the code values and type indicator.
2. All your codes now need to be of the same data type - usually this means varchar2. If your code is "naturally" a numeric type, you will either have to do data conversion or forsake the natural type of the data
3. I think it goes against all the advice about how to model your data
   • a code table with codes representing different things breaks the whole idea of tables representing a single type of entity.
4. It means your code tables can be a lot larger than they need to be. this can affect performance in extreme cases. You often require multiple indicies on the table because your code value may not be unique or you have to use a composite key consisting of the code and its type. - not really a problem, but it does seem to make things more complicated than needed.
5. Your DML can be more complicated because you now need to explicitly keep track of the code type information. There is a greater chance of errors if developers neglect to include this code type info.
6. I find the use of a single code table often results in the "codes" been constrainted/modeled on the needs/restrictions of the code table itself rather than on the domain you are modeling.

If I think about it more, I can probably come up with a lot more reasons. The only arguments for a single code table seems to be that its easier to maintain 1 table rather than many and data input may be easier. However, I'm not convinced by these arguments. Code tables have a very low maintenance cost in my experience and they are often of a very similar format, so you can usually re-use large amounts of code used to handle data input/update to the code tables.

I have been working with an application which has a large single code table and I find the additional work required in DML because of this multi-type code table is a constant frustration. In this particular application, new codes are rarely added (a couple of times a year and not that many even then) and existing codes are never changed. Much of the SQL used by the application looks a lot more complex and is often more difficult to deconstruct simply because of the additional qualifiers required whenever the coe table is referenced in any way.

Tim Received on Thu Nov 21 2002 - 15:53:43 CST