Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    3

    Store Product vs. Abstract Item

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Posts
    54

    No Problem

    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
    Attached Thumbnails Attached Thumbnails items_and_stores.gif  

  4. #4
    Join Date
    Jan 2005
    Posts
    3
    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?

  5. #5
    Join Date
    Dec 2004
    Posts
    54

    Subtypes and Subtypes

    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

  6. #6
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Here's your data model with subtypes
    Attached Thumbnails Attached Thumbnails subtypes.JPG  

  7. #7
    Join Date
    Jan 2005
    Posts
    3
    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.

Posting Permissions

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