Re: composite foreign key one field constant?

Code tables are very simplistic. Once you set them up you rarely have to change them. And they require very little maintenance otherwise. So, whether you have one or one hundred does not significantly change the DBA work load.

On the other hand, if you are properly designing the rest of your tables, you have to carry two columns everywhere you reference the code table. Even if, as in your original problem, the type is constant. Having twice the number of columns causes more work for the DBA and easily offsets the reduced number of tables.

The data entry argument depends on your application development tool and the skill of your application developer. If your code tables follow a pattern, then changing the table and columns behind the form is not difficult. The tools I have used, VB and PowerBuilder, allow you to easily do this. Code tables have relatively low volatility, so performance is generally not an issue.

The real cost of this implementation lies in the additional complexity of the select, insert, update and delete statements. Again, you now have to code two columns everywhere instead of one. The length of the statements is much longer and prone to error. You have to remember a meaningless type value rather than simple table name. The database cannot check the type value like it can a table name.

The increased length of the SQL statements also has a negative impact on performance, too. You need more network bandwidth and a larger query cache to handle the larger statements. Also, putting all the codes in a single table creates a hot spot in your database.

The bottom line is that the single code table approach does not save work. I either shifts the work to another part of the database design or shifts the work to the SQL statements.

And finally, from a modeling perspective this approach is wrong, too. Your different code types are different things and should be in different tables. You need to follow the Ben Franklin adage of "a place for everything and everything in its place" when designing databases.

"Don Dwoske" <ddwoske_at_yahoo.com> wrote in message news:32001f08.0211210627.1869956d_at_posting.google.com...
> 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.
>
> To me having one value table to maintain is easier than 20. I would
> be curious to read any articles or design patterns
> for this type of thing, but simply didn't find any.
>
> I have read some boards of people doing what I am doing
> with great success. It makes maintaining and extending the
> application value table easier because it's all in one place and can
> thus be handled by one form. It's easier to query controlled vocabulary
> from an app point of view also, because it's all in the same place.
>
> One drawback is the problem I am currently having, but it's
> certainly not a show-stopper. The administration code needed to
> maintain this table is far less than if I had many tables, so at
> the moment, the tradeoff (to me) seems worth it.
>
> If either naysayer could expand on your opinions and present a case,
> other than saying it's a design flaw, I would really like to
> hear it... after all, we're all trying to build great applications and
> I would hate to head down this road if it's really a bad idea.
>
>
> Cheers,
> Don
>
>
>
> Tim Cross <tcross_at_pobox.une.edu.au> wrote in message
news:<87zns33nrf.fsf_at_blind-bat.une.edu.au>...
> > "Brian E Dick" <bdick_at_cox.net> writes:
> >
> > > BTW, I have worked with this implementation before and in the long run
it's
> > > a real bastard. Looks like you are trying to avoid multiple code
tables by
> > > merging them into one and adding a type column. Don't do it. Use
multiple
> > > tables. Otherwise, this "simplification" will cost you tons of
complicated
> > > and buggy SQL down the road.
> > >
> >
> > Oh how I wish this point could become more widespread.
> >
> > Out of all the db applications I have worked on over the years, this
> > would be one of the most common design flaws I have come across. I
> > find it difficult to understand why so many designs incorporate the
> > single code table with a type field approach when all the theory
> > indicates it is a bad idea (tm) and anyone who has had to work with
> > such a system knows it leads to additional complications, maintenance
> > problems and a common source of errors.
> >
> > Tim
Received on Thu Nov 21 2002 - 09:51:39 CST