database design

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • DB
    #1

    database design

    Hello All,

    I'm failling 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.

    Cheers,
    D.


    Tags: None
    • Pavel Romashkin
      #2
      Re: database design

      I think you need 3 tables:

      ProductGroups
      GroupID (PK)
      GroupName
      GroupDesc

      Products
      ProductID (PK)
      Maker
      etc...

      ProductsAndGrou ps
      ProductID (FK)
      GroupID (FK)
      other info...

      In the ProductsAndGrou ps junction table, set the PK to the combination
      of ProductID and GroupID.

      Cheers,
      Pavel

      DB wrote:[color=blue]
      >
      > Hello All,
      >
      > I'm failling 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.
      >
      > Cheers,
      > D.[/color]

        Comment

        • MeadeR
          #3
          Re: database design

          I think you need to set up a many-to-many association - where in most
          cases an item is only assoc. with one Group, but could also be assoc.
          with multiple groups.....it means an extra table but keeps the two
          'data' tables clean

          "DB" <%64%69%7a%7a%7 9%62%69%72%64%4 0%64%69%7a%7a%7 9%2e%63%78> wrote in message news:<vqi5tqhur b9oc3@corp.supe rnews.com>...[color=blue]
          > Hello All,
          >
          > I'm failling 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.
          >
          > Cheers,
          > D.[/color]

            Comment

            • Pieter Linden
              #4
              Re: database design

              "DB" <%64%69%7a%7a%7 9%62%69%72%64%4 0%64%69%7a%7a%7 9%2e%63%78> wrote in message news:<vqi5tqhur b9oc3@corp.supe rnews.com>...[color=blue]
              > Hello All,
              >
              > I'm failling 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.
              >
              > Cheers,
              > D.[/color]

              You'll need 3.

              Product--(1,M)--ProdGroupMember ship--(M,1)--ProductGroup

              CREATE TABLE ProdGroupMember ship(
              ProductID As Long,
              GroupID As Long,
              PRIMARY KEY (ProductID, GroupID)
              FOREIGN KEY ProductID REFERENCES Product(Product ID),
              FOREIGN KEY GroupID REFERENCES ProductGroup(Gr oupID));

                Comment

                Previous template Next
                Working...