Is this good a design?

From: James_One <donotreply_at_interbulletin.bogus>
Date: Tue, 06 Mar 2001 17:54:18 +0000
Message-ID: <3AA5244A.4652CEC0@interbulletin.com>

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?
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?

Thanks to all.

---

Submitted via WebNewsReader of http://www.interbulletin.com Received on Tue Mar 06 2001 - 11:54:18 CST