Re: Is this good a design?

I thought this is a typical many-to-many relationship.

If you don't like the cartItemTYPE, then, perhaps you can break this table into two, namely, cartID + ItemID
and cartID + PackageID.

-Samuel.

"James_One" <donotreply_at_interbulletin.bogus> wrote in message news:3AA5244A.4652CEC0_at_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 Wed Mar 07 2001 - 06:39:02 CET