Within a product catalog, different categories of products have different attributes.
Take a restaurant for example. Ice cream can be ordered with a choice of flavor and cup or cone. A pizza can be ordered with a choice of size and choice of crust. To model these different products, we would have to maintain a product table and a separate attribute table, with a one-to-many relationship.
First, we have the product types table - pizza and ice cream are two of these types.
ProductType Table| ID | Name |
|---|---|
| 10 | Pizza |
We then define the attribute names and possible attribute values for each product type
Attribute Table| ID | Name | ProductTypeID |
|---|---|---|
| 50 | Size | 10 |
| 51 | Crust | 10 |
| ID | Name | AttributeID |
|---|---|---|
| 80 | Small | 50 |
| 81 | Medium | 50 |
| 86 | Pan | 51 |
| 87 | Deep Dish | 51 |
Each product has an entry in the Product table, with a reference to the product type.
Product Table| ID | Name | ProductTypeID |
|---|---|---|
| 1 | Hawaiian Pizza (Small, Pan) | 10 |
Against each attribute, we have the value selected for the product.
ProductAttributes Table| ProductID | AttributeValueID |
|---|---|
| 1 | 80 |
| 1 | 86 |