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.

re: database design

I think you need 3 tables:

ProductGroups
GroupID (PK)
GroupName
GroupDesc

Products
ProductID (PK)
Maker
etc...

ProductsAndGroups
ProductID (FK)
GroupID (FK)
other info...

In the ProductsAndGroups 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]

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%79%62%69%72%64%40%64%69%7a%7a%79%2e% 63%78> wrote in message news:<vqi5tqhurb9oc3@corp.supernews.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]

re: database design

"DB" <%64%69%7a%7a%79%62%69%72%64%40%64%69%7a%7a%79%2e% 63%78> wrote in message news:<vqi5tqhurb9oc3@corp.supernews.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)--ProdGroupMembership--(M,1)--ProductGroup

CREATE TABLE ProdGroupMembership(
ProductID As Long,
GroupID As Long,
PRIMARY KEY (ProductID, GroupID)
FOREIGN KEY ProductID REFERENCES Product(ProductID),
FOREIGN KEY GroupID REFERENCES ProductGroup(GroupID));