Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Question The right way to do it

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: The right way to do it

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

  3. #3
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68

    Re: The right way to do it

    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)

  4. #4
    Join Date
    Sep 2003
    Posts
    2

    Re: The right way to do it

    Originally posted by mashinovodja
    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

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: The right way to do it

    Originally posted by Jonathan_Beni
    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.

  6. #6
    Join Date
    Sep 2003
    Posts
    2

    Re: The right way to do it

    Thank you, thank you, thank you!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •