Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    normalisation and design for products db

    I'm failing to get my head around a normalisation and db design issue, and was hoping someone may be able to offer some comments.
    I have two tables ProductGroups and ProductItems.
    ProductGroups has a Manufacturer, Model, and Image (cutting it to basics)
    ProductItems has a MasterID field - relating to a ProductGroup so that each item can be associated with a particular ProductGroup, however, although the majority of items are specifically associated with one ProductGroup some items are more general and can be associated with any number of ProductGroups.
    I'm not sure how to address this in the best manner, as I don't want to be repeating data as will be the case currently. Will it be best to have one productitems table for specifically related items and one for the more general items? The more I think about it the more I seem to confuse myself!
    Any help greatly appreciated.
    D.

  2. #2
    Join Date
    Nov 2003
    Posts
    1
    My friend it looks as if you have a many to many relationship here even though its only some of the time that their are general items not listed in the Products Group table. I believe you will need to have an intersection table to split this M:N relationship. Example is Sale,LineItem and Items. In this example there can be many items to LineItem and many LineItems in Sales. Hope this helps! I am a newbee at this as I am still completing my MIS Degree but I believe I have this concept down.

  3. #3
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    To be be more specific for your problem. You will need another table to link the ProductGoups to the ProductItems. This table will have a PK made up of two FK links --- PK of ProductGroups and PK of ProductItems

    Something like this:

    ProductGroups(group_id, Manufacturer, Model, [other fields]...)
    ProductGroupItems(group_id, item_id)
    ProductItems(item_id, [other fields]...)

  4. #4
    Join Date
    Nov 2003
    Posts
    2

    Cheers

    Thanks Guys,

    That works splendidly.

    D.

Posting Permissions

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