Re: composite foreign key one field constant?

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 - 08:27:51 CST