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.

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.

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.

It works, but the other way round: delete the product, and the associated book or whatever gets deleted too.