I'm having trouble deciding how I should proceed in designing my database.
What I want is to maintain a Store Front separate from my Inventory. Part of the reason for this is that I want to maintain my inventory in one place, but I will eventually have several stores selling different products form the same list of inventory. I also have several products that refer back to the same type of thing, i.e. the metadata is the same.
Currently, I'm working with the notion of "Products" are in the Store and "Items" represent the inventory. For example, an Album would be an item in my inventory, it has metadata for artist, label, etc. In the Store the abstract item (album) would manifest itself as multiple products CD, LP, Digital Album.
Now, this all works fine, until I introduce the concept of an album track, which is logically a child of the Album table and could also be sold as a "digital track" product. Would I make a track an "item" too? It makes me feel funny.
What are your thoughts on such a database design?
How do people typically model a situation where you have an Item but there could be any number of different types each requiring their own custom data?
Any comments would be greatly appreciated!! Thanks for the help.
Thank you both very much. I agree, I think the Track should be an Item as well. The picture also makes things much clearer. I hadn't thought about having a join table for the various stores; that's much cleaner.
What do you think about the myriad of detail tables for each specific type of item? Is that a common way to do things?
Well this is certainly reassuring. This is almost verbatum what I designed after reading the suggestions in this thread.
I guess a couple of things made me feel funny at first...
1) Using item_id as the key in the "detail" tables. But then I got to thinking about it and said; there's no point in having another key for an album (album_id, item_id). An album is indeed an item, so just use item_id by itself.
2) While I could create foreign keys to ensure that the item_id in the album table appeared in the actual item table. I could not create a foreign key to ensure that the same item_id was not repeated in any of the other "detail" tables. I finally chalked that up to "well I'll just have to not screw that up.".