Got what should be a fairly simple normilization problem.
Basically I am creating a small quoting system for my company. A quote consists of product items - fairly standard. What I would also like to have is 'groups of products'.
A 'Product Group' would just be a number of items such as a cd and a cd case combined under one price and one name. Where someone could order cd's by themselves, but someone else wants to buy cd's and cases together, all we have to do is enter the product group code instead. This is a very simple example, and you may think what's the point, but often we'll have product groups that consist of 10 products together. We'd rather just have to enter one code. We'd also like to have special group prices etc.
I'm thinking something like.
Quote
id
date
tax
QuoteItem
id
quoteid
productid
price (copied so prices can vary)
Product
id
code
name
price
ProductGroup
id
name
ProductGroupItem
id
code
name
productgroupid
productid
My dilema is this:
I'd like to have product groups listed on the products table, so on my form (I'm using PHP & MySQL) I just query the one table for my list of products. I would also think having two tables linking products to quotes and productgroups to quotes seems a little excessive.
What is the best way to tackle 'products' and 'productgroups'? Your suggestions would be great appreciated.
