Retail Database Design - Multiple Products with different accessories

Question

I am trying to design a MySQL database for an existing content-on-page retail website. We have multiple products by different manufacturers, and we have different accessory options for each product, although some share the same accessories. I'm stuck on designing the accessories TABLE(s)

Product 1 has 23 accessories - 9 color options, 4 door options, 4 leg options, 4 trivet options, 1 variable speed blower option, 1 fire screen option.

Product 2 has - 4 door options, 4 leg options, 1 variable speed blower option, 1 fire screen option (shares these options with Product 1). Product 2 also has a leg adapter option, a pedestal option, and two ash drawer options.

Product 3 has 27 accessories - 9 color options, 8 door options, 4 leg options, 4 trivet options, 1 variable speed blower option, and 1 fire screen option. The color options share the same post-fix, but have a different prefix (prod1.BLUE / prod3.BLUE). The leg options are the same for all products. The blower and fire screen are different from Product 1.

Do I need a separate accessories TABLE for each model? Thanks in advance for the assistance, and if I have committed any faux pas, my bad. This is my first question, please go easy! :)

Answer 1

UPDATED ANSWER

My original answer missed the point about the needing to "share" accessories, what with the question being about having a SEPARATE table for each model. It was my misunderstanding.

If you need to "share" accessories with two or more models, that implies a many-to-many relationship. That is,

• a model can have zero one or more accessories.

• an accessory can be related to zero one or more models.

To resolve a many-to-many relationship like this, we add another table, and remove the model_id column from the accessories table.

A row in the table will represent the relationship between an accessory and a model. This table will have a foreign key (model_id) to the models table and a foreign key (accessory_id) to the accessories table.

So, the accessories table would be a list of all available options:

accessories
id  description
--  --------------------
 1  color option black
 2  color option blazing red
 3  color option burnt umber
 4  color option chocolate
 5  color option deep red
 6  door option 1
 7  door option 2
 8  good blower
 9  quieter blower
10  fancy legs
11  fancy dancy legs

If model 101 has accessories: color options of black and burnt umber, and fancy legs, the rows in the model_accessories table would look like this:

model_accessories
model_id accessory_id
-------- ------------
     101            1
     101            3
     101           10

model_id 101 is a reference to the id column the models table, and the accessory_id is a reference to the id column in the accessories table.

Model 102 could share some of those same accessories, as well have different ones:

     102            1
     102            2
     102            3
     102            4
     102           11

Usually, there is a UNIQUE constraint placed on the combination of (model_id, accessory_id), so the same accessory isn't associated two (or more) times.

Sometimes, there are attributes on the relationship. For example, the list price of an accessory may be different on different models. To support this, an attribute column (list_price could be added on the model_accessories relationship table.

The target of the foreign keys don't necessarily need to be separate tables; these could both reference the same products table. (That's why I chose to use model and accessories as table names in my examples.)

If we consider both models and accessories to both be products, then these can be stored in the same products table.

So, a products table could be:

products
id   description
---  --------------------
101  trail blazer
102  cabin inferno
103  cottage conflagration
  1  color option black
  2  color option blazing red
  3  color option burnt umber
  4  color option chocolate
  5  color option deep red
  6  door option 1
  7  door option 2
  8  good blower
  9  quieter blower
 10  fancy legs
 11  fancy dancy legs

The model_accessories table would remain the same, but both columns in that table would be foreign keys to the products.id column.

ORIGINAL ANSWER (downvoted, no reason given, but likely because it missed the point about "sharing" accessories. The approach in the answer below assumes that each accessory is associated with exactly ONE model. I thought the OP was meaning to create a model_101_accessories table, a model_102_accessories table, with nothing shared.

---

No, you don't need a separate table, if you store the accessories as rows in the table, rather than as columns.

Include the model_id in the accessories table, so you know which accessories go with which model.

model_accessories
id  model_id   description
--  --------   --------------------
 1       101   color option black
 2       101   door option 1
 3       101   good blower
 4       101   quieter blower

 5       102   color option 1
 6       102   color option 2
 7       102   color option 3
 8       102   color option 4
 9       102   door option 1
10       102   door option 2
11       102   fancy legs
12       102   fancy dancy legs

All the options are together in one table. But the rows where model_id=101 are associated with the product model id=101. The rows where model_id=102 are associated with the product model row with id=102.

A foreign key constraint can enforce that the only values that are allowed in the accessories.model_id column are values that are found in the model.id column.

Answer 2

You don't need a separate table for each product -- that's madness. You describe a many-many join, which is usually implemented using a table that can join accessories to product/model. Assuming you have IDs for each product and accessory:

| product | accessory |
| 5       | 4         |
| 5       | 5         |
| 5       | 6         |
| 5       | 7         |
| 6       | 4         |
| 6       | 5         |
| 6       | 6         |
| 6       | 7         |
| 6       | 8         |
| 6       | 9         |

In this table, product 5 (say, an iPad 2) can have accessory 4, 5, 6, and 7 (covers). Because the way these covers are designed, they also work with product 6 (an iPad 3). Accessories 8 and 9 are designed specifically for product 6, and won't work with product 5.