Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006

    3 types of items under one title, how to proceed?

    Hello, I'm new here and a total n00b at desinging databases, so please bear with me.

    I can't figure out how to design this effectively:

    I have a product title that has several types of stuff under it. So for example I have a title called Matrix. I then have Matrix the movie, Matrix the PC-game and Matrix the PS2-game. There are several hundreds of other titles as well with a similar situation.

    Currently all of the different item types have separate tables, like this:

    MOVIE {title, attributeA, attributeB. attributeC}

    PCGAME {title, attributeD, attributeE}

    PS2GAME {title, attributeF}

    (Sorry about the horrible syntax, I bet it doesn't conform to the standards of any DBML)

    Note that they do not share any attributes except the title. All of these tables are under the ARTICLE_ID class/table, which identifies an item(not a title, but an invidual item, so there might be multiple copies of an item with the same title, each with a different ARTICLE_ID) by it's number and holds status information.

    However, this construction leads to ugliness. There are 3 foreign keys "title" tied to the ARTICLE_ID table and I'm not sure how my queries will behave once I start implementing this. The DB will be realised with MySQL. Any pointers or suggestions appreciated. Hopefully somebody can understand something from this explanation.

    I'll try to clarify this if need be, it just seems that I left my design sketch to my school computer where I can't reach it right now.

  2. #2
    Join Date
    Feb 2005
    Colorado Springs
    This looks something like a retail inventory problem. A good resource for ready-made data models is I wouldn't get too focused on the fact that items have a similar title - they are actually different entities. Perhaps a structure something like the following:

    Inventory_ID (PK)
    Type (FK, referencing Item_Types table)
    Additional Info (e.g., price)

    Item_Type_Key (PK)

    Hope that gets you started!

  3. #3
    Join Date
    Mar 2006
    You are correct, it IS a retail inventory problem. However, it is just an exercise, although the end result is a fully functional database that will be populated with data.

    I appreciate the suggestion, I figured it should look something like that and it seems to fit to the rest of the model perfectly.

Posting Permissions

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