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.

So originally I thought about the following

item
item_id
item_type (album, tshirt, etc)

item_album
item_id
<custom metadata>

item_tshirt
item_id
<custom metadata>

map_item_product
item_id
product_id

product
product_id
product_type (cd, lp, digital_album, digital_track...)
<product details>

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.

in merchandising, a pair of socks has a sku (stock keeping unit) number

and a 6-pack of assorted socks has a different sku

same product, different offering

i would have the track as a separate item from the album

the tricky part will be "relating" the two, eh

(e.g. search for track title should turn up the track and the album -- but this can only happen if the track is related to the album)

Hi,
This is a common design. Terminology asside (call the entities what you'd like)
See attached picture.

The trick is to be certain you get the attribtues correct.

Enjoy!
Vmusic

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?

Hi Noraz,
So here's your problem right. You have a logical entity called 'item' and there's a lot of subtypes. This is because every item is different.

Coffe Cups don't come in Small, Medium and Large
Clothes don't come in 2QT (or 2L)

That's the very purpose of logical modeling really. Make those subtypes in a logical model, you'll start off with product categories or major subtypes ,and then branch from there.

This exercise will help you define what you REALLY honestly need to track.

Then you decide how to transform your logical model into a physical model.

I think you'll find items have a lot more in comon than you think?
Vmusic

Here's your data model with subtypes

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.".

Excellently help folks. I really appreciate it.