Re: Is this good a design?

James_One wrote:
>
> I have a question about a relationship that I've used before and was wondering how correct it was:
>
> Assuming I have the following tables for an online-store db:
>
> CREATE Table Items
> (ItemID INTEGER NOT NULL PRIMARY KEY,
> ItemName CHAR(50) NOT NULL,
> ItemPrice MONEY NOT NULL);
>
> CREATE Table Packages
> (PackageID INTEGER NOT NULL PRIMARY KEY,
> PackageName CHAR(50) NOT NULL);
>
> CREATE TABLE Packages_Items
> (PackageID INTEGER NOT NULL
> REFERENCES Packages(PackageID)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> ItemID INTEGER NOT NULL
> REFERENCES Items(ItemID)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> Quantity INTEGER NOT NULL,
> PRIMARY KEY (PackageID, ItemID));
>
>
>
> Both Items, and Packages made up of those Items, can be purchased.
> The table Packages_Items stores the packages and their member items.
>
>
> I also have a table of shopping cart contents as such:
>
>
> CREATE TABLE CartContents
> (CartID INTEGER NOT NULL,
> ItemID INTEGER NOT NULL
> REFERENCES Items(ItemID),
> Quantity INTEGER NOT NULL,
> PRIMARY KEY (CartID, ItemID));
>
> Now the above would work fine if the customer could only place Items in his or her cart, but the cart needs to hold items OR packages.
>
> In the past I've set up the CartContents table like this:
>
> CREATE TABLE CartContents
> (CartID INTEGER NOT NULL,
> CartItemID INTEGER NOT NULL,
> CartItemType CHAR(1) NOT NULL CHECK (CartItemType IN ('P', 'I')),
> Quantity INTEGER NOT NULL,
> PRIMARY KEY (CartID, CartItemID, CartItemType));
>
>
> In this way the CartItemId can reference either an ItemID or a PackageID, tracked with CartItemType.
>
> This has worked fine but I'm wondering a few things:
>
> 1) What is this type of relationship called when an attribute is a FK to multiple tables?

Well, in a way you are introducing a superclass to item and package, you are just not implementing this superclass in a seperate table.

> 2) Is this a good design, or is there a better way?

There was a lengthy discussion on this only e few weeks ago. There are pros and cons. Personally I find it to be godd design (although I would implement the superclass).

> 3) If it is good design, how can you set up the referntial integrity constraints for CartItemID?

By implementing the superclass in a table. Of course this only passes the problem on, because how are you to enforce referential integrity from the superclass to the subclass? The simple answer is: you can't. A more elaborate answer can be found in the above mentioned discussion.

--
Kristian Damm Jensen              | Feed the hungry. Go to 
kristian-damm.jensen_at_capgemini.dk | http://www.thehungersite.com