Re: Is this good a design?

On Tue, 06 Mar 2001 17:54:18 +0000, James_One <donotreply_at_interbulletin.bogus> wrote:
>1) What is this type of relationship called when an attribute is a FK to multiple tables?
>2) Is this a good design, or is there a better way?
>3) If it is good design, how can you set up the referntial integrity constraints for CartItemID?

Don't know what it's called.

Looks OK to me, except that I generally dislike using simple integers as PK.

I would proceed differently. Note that if you use distinct domains for itemPK and packagePK, say that itemPK -- an atomic field, let's say a char() -- always starts with an I, and the package PK always starts with a P, then you don't need the separate item type field in the cart table. Of course, your queries will need to do unions across item and package tables, but I think that's "clean" in design and theory, even if it costs something at execution.

Now, just how you generate the typed PKs automagically, that can be a problem. If you have a before trigger, you can usually do it there. Again, it does cost a bit more than just using an integer/identity field. Or, you can do it externally - in a generatePK stored procedure, preferably.

FWIW. Joshua Stern
JRStern_at_gte.net Received on Tue Mar 06 2001 - 21:37:06 CET