Table design help needed

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Todd Hazer (NO_SPAMthazer_at_gmail.com)
Date: 10/21/04 

Date: Thu, 21 Oct 2004 11:00:32 -0700

I'm trying to figure out how to design my database to store entities in
their own tables and then allow the attributes of these entities to be
compared to each other for querying.

So, in the example below I have three entities, WaterBottle, CoffeeMug
and a WaterBottleCap. In the three entity tables I have various
different entities of Bottles, CoffeeMugs and Caps with different
Diameters and CapTypes. All of these entities inherit the common
attributes from the Products table which contains the primary key/id for
all products. I now add a couple WaterBottles, CoffeeMugs, and Caps to
the ProductSets table.

This gives me a ProductSets table that looks something like this.

ProductSets Table:

ProductSetID ProductID
----------------------------------------------------------------------------
1 5 -- This is a WatterBottle
1 9 -- This is a WatterBottle
1 8 -- This is a CoffeeMug
1 4 -- This is a CoffeeMug
2 5 -- This is a WatterBottle
2 9 -- This is a WatterBottle
2 8 -- This is a CoffeeMug
2 4 -- This is a CoffeeMug
----------------------------------------------------------------------------

Now what I want to happen is a user chooses a Cap (say CapID 2). Next
the user chooses a one of many predefined ProductSets to use for
choosing their next product So in this case Set 1 is chosen. After the
set is chosen the user then sees all the WaterBottles and CoffeeMugs
that have the same Diameter and CapType as the Cap originally chosen.

What I can't figure out how to do is query the matching WaterBottles and
CoffeeMugs from their entity tables. I think the reason why this is
difficult is that ProductSet.ProductID should be a foreign key to the
primary key of the an entity table, it's not because I need to store the
id's from many entity tables in the ProductSet.ProductID column. Does
anyone have any idea how this might be accomplished?

CREATE TABLE Products (
        ProductID int identity(1,1) NOT NULL, -- Primary Key/ID for all products
        ManufacturerID int NOT NULL,
        Model varchar(50) DEFAULT ('') NOT NULL
        -- More attributes that all products use.
)
GO

CREATE TABLE WaterBottles (
        WaterBottleID int NOT NULL, -- Foreign key to Products.ProductID
Diameter decimal (4,2) DEFAULT (0) NOT NULL,
        CapType char (3) DEFAULT ('') NOT NULL
        -- More attributes that are different from other entities.
)
GO

CREATE TABLE CoffeeMugs (
        CoffeeMugID int NOT NULL, -- Foreign key to Products.ProductID
        Diameter decimal (4,2) DEFAULT (0) NOT NULL,
        CapType char (3) DEFAULT ('') NOT NULL
        -- More attributes that are different from other entities.
)
GO

CREATE TABLE Caps (
        CapID int NOT NULL, -- Foreign key to Products.ProductID
        Diameter decimal (4,2) DEFAULT (0) NOT NULL,
        CapType char (3) DEFAULT ('') NOT NULL
        -- More attributes that are different from other entities.
)
GO

CREATE TABLE ProductSets (
        ProductSetID int identity(1,1) NOT NULL,
        ProductID int NOT NULL,
)
GO

Thanks,

Todd