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

    Connecting one table to many

    Ive been having trouble coming up with a good design for a database I'm working on. The database is supposed to include support for multiple product lines such as CDs, Toys, Books, Games, etc. Separate tables have been made to accomodate them and things such as Publishers, Authors, Manufacturers, and so on. We have been using foreign keys to link tables together to create cascading deletes. What we want to do to finish it is have one general stock table that has a stockid that connects to a stockid in the main tables for books, cds, games and others. We need this so that if a Publisher is deleted it will cascade down to all the books of that publisher and then cascade down to the stockitem table and get rid of all the stockids that were just deleted from that books table.

    The problem is that it does not seem possible to create a foreign key on stockid and have it reference the stockid in the books, cds, toys and games tables. Is there any practical way to get these separate tables to link into one stock table? One method that does work is having the separate tables link to the stockitem table. In this way when a stockitem is deleted it cascades down and gets removed from the book, cd or whatever table. Unfortunately when you delete a book it does not cascade to the stockitem and remove it. How would a real company like amazon handle multiple product lines? Thanks for the help.

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

    Re: Connecting one table to many

    Here is one approach:

    Create a table Product to represent all products (books, CDs, games, etc.) This table will include a product_type attribute to state whether it is a book, CD or whatever.

    If it is still necessary to have separate tables for Book, CD, etc., then each of these tables should have a mandatory foreign key referencing Product, and that foreign key should also be the primary key (i.e. there is a one-to-one relationship between Book and Product, CD and Product etc.)

    Now link your Stock_Item table to Product. This way a Stock_Item may be linked to a Book, CD or whatever - but always via the generic Product table.

    BTW, I am concerned that you seem to intend to overuse the cascade delete capability of foreign keys. Normal practice is for foreign keys to be set to restricted, so that you cannot e.g. accidentally delete a publisher who has published 50 books that you have sold to 1000 people, and wipe out all that data. Delete cascade should be used sparingly, typically where the child data is merely further description of the parent table rather than important data in itself. With the model I just described above, the foreign keys from Book to Product, CD to Product etc. would be good candidates for delete cascade.

  3. #3
    Join Date
    Sep 2003
    Posts
    5
    The Product table that states the type of item we already have created, we just called it StockItems. My existing design has a foreign key in each of the separate product tables so they link to StockItems. The problem with that is that when you delete a book or cd or whatever it will not cascade to the product table and remove the item since everything references the product table. You can only go the other way. I know one would argue that you might want to block this type of behavior but this design is for a class where we are demonstrating knowledge of cascading deletes and are supposed to have a way of cascading down to the product table. I guess this type of behavior is not possible though.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    It works, but the other way round: delete the product, and the associated book or whatever gets deleted too.

Posting Permissions

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