Hi, Question...

I'm wondering if this is the best approach to setting up a multiple primary/foreign key relationship.

I have a "Category" table which contains a CatId and Category name, this table contains the primary key (CatId). I have several other tables which use the CatId as a foreign key. In these tables some of the items relate to 2 of the primary keys in the category table so I set up 2 columns to allow 2 foreign keys per item. I tried to just add a comma-deliminated list to a single column but it failed.

So my question is this, is this a common way to set up a multiple foreign key relationship?

Thanks, Dan

FYI, I'm using MS SQL 7

Yes, a column for each foreign key is right. A comma-delimited list would violate first normal form (and doesn't work).

As you describe it, you have a many-to-many relation between categories and items: one item can fall into many categories, and one category can include many items.

If the number of possible categories is very small (say, up to 3) you can add the corresponding number of category fields in the items table.

If the number of categories is/becomes bigger, then you will have to convert the many-to-many relation into two one-to-many relations using a "connection" table:

Item(PK=ItemID, ...)
Categories(PK=CategoryID, ...)
ItemCat(PK=ItemID, CategoryID)

Let me ask a follow up on that reply...

Now that I have a structure similar to this one how can I retrieve Items that have more than one category?

CategoryID = 1 AND CategoryID = 2, for instance

Thanks

There are various ways:

SELECT i.*
FROM items i
WHERE EXISTS
(SELECT 1 FROM item_categories ic
WHERE ic.item = i.item AND ic.category=1)
AND EXISTS
(SELECT 1 FROM item_categories ic
WHERE ic.item = i.item AND ic.category=2);

SELECT i.*
FROM items i
WHERE EXISTS
(SELECT 1 FROM item_categories ic1, item_categories ic2
WHERE ic1.item = i.item AND ic1.category=1
AND ic2.item = i.item AND ic2.category=2);

SELECT DISTINCT i.*
FROM items i, item_categories ic1, item_categories ic2
WHERE ic1.item = i.item
AND ic1.category=1
AND ic2.item = i.item
AND ic2.category=2;

SELECT i.*
FROM items i
WHERE i.item IN
( SELECT item FROM item_categories WHERE category=1
INTERSECT
SELECT item FROM item_categories WHERE category=2
);

...etc.

Thank you, thank you, thank you!!