Using a many-to-many table with a one-to-many relation?

Question

I'm building a platform where Suppliers can add products, and shop owners can 'copy' those products to their shops and sell them.

A Supplier can create products, and he can add all sorts of attributes to them, like the size, colors.

When a Shop wants to use one of the Supplier's products, he can choose which attributes he wants to offer. For example if the Supplier has a T-Shirt in offer with 3 sizes. The shop may choose to sell that T-Shirt with only 2 sizes.

So I have a Products table which looks like this. The Supplier adds products in here.

+----+-------------+-------+
| id | description | price |
+----+-------------+-------+
|  1 | T-Shirt     |    10 |
|  2 | Car         |   100 |
+----+-------------+-------+

I have a Product attributes table, Which is also filled by the Supplier

+------------+-------------+------------------------------+
| product_id | description |           options            |
+------------+-------------+------------------------------+
|          1 | size        | ["small", "medium", "large"] |
|          1 | color       | ["white", "black"]           |
+------------+-------------+------------------------------+

Shop Side

When a Shop owner wants a Product. I make a record in the shop_products table, referencing which Shop wants what Product, and adding an optional image, as following

+---------+------------+-----------+
| shop_id | product_id |   image   |
+---------+------------+-----------+
|       1 |          2 | image.jpg |
|       1 |          3 | image.jpg |
+---------+------------+-----------+

Now the only issue remaining is: How do I make sure the Shop can choose which attributes he wants to take.

• Should I create a shop_product_attributes table.. But I can't put a relation on the many-to-many because it doesn't have an id?

Answer 1

Your customers may not offer every possible attribute, right? E.g., "T-Shirt, Cotton" is available in small, medium, and large, but my store only offers small and medium. Maybe even black shirts in small and white shirts in small and medium, but not black shirts in medium. To support that, you need a table which normalizes attributes (one record per possible value, and you need to join your Shops table to that.

So suppliers would populate these tables:

CREATE TABLE Products
(
ProductID   INT NOT NULL PRIMARY KEY,
SupplierID  INT NOT NULL,
CategoryID  INT,
Description VARCHAR(100) NOT NULL,
BasePrice   DECIMAL(18, 2) NOT NULL,
Image   VARBINARY(MAX),
UNIQUE (SupplierID, Description)
)

CREATE TABLE ProductAttributes
(
AttributeID INT NOT NULL PRIMARY KEY,
ProductID   INT NOT NULL,
Dimension   VARCHAR(100) NOT NULL,
Value   VARCHAR(100) NOT NULL,
PriceModifier   DECIMAL(18, 2) NOT NULL DEFAULT 0.00,
Image   VARBINARY(MAX),
UNIQUE (ProductID, Dimension, Value)
)

Shops would then populate two tables to show what they offer, one for products and one for each intersection of attributes. It'll be important that the UI allow store owners to quickly add all attributes; you don't want them ticking a box for every record.

CREATE TABLE ProductsOffered
(
ShopID  INT NOT NULL,
ProductID   INT NOT NULL,
PRIMARY KEY (ShopID, ProductID),
Description VARCHAR(100) NOT NULL,
Image   VARBINARY(MAX),
BasePrice   DECIMAL(18, 2) NOT NULL
)

CREATE TABLE AttributesBuckets
(
ShopID  INT NOT NULL,
BucketID    INT NOT NULL,
AttributeID INT NOT NULL,  -- Implies ProductID
PRIMARY KEY (ShopID, BucketID, AttributeID),
Image   VARBINARY(MAX),
PriceModifier   DECIMAL(18, 2) NOT NULL DEFAULT 0.00,
)

So if a store decides to sell T-shirts in "black" and "small" there will be a record in AttributesBuckets for ShopID = MyShop, ProductID = T-Shirt, BucketID = 1, AttributeID = color/black, and another record with AttributeID = size/small. That's one "bucket;" one combination of attributes which fully describes a product that can be sold. If they also sell small white shirts, that's one more bucket, defined by two more records. If you add a "stitching" option, each bucket will have three records, for color, size, and stitching.

As a bonus, you can now track inventory:

CREATE TABLE Inventory
(
ShopID  INT NOT NULL,
BucketID    INT NOT NULL,
PRIMARY KEY (ShopID, BucketID),
Quantity    INT NOT NULL DEFAULT 0
)

Answer 2

Kumar Harsh's basic suggestion (instead of shop_product representing a simple link between shop and product, have it hold the attributes for the product that this shop will actually offer) does have a couple of potential issues:

1. If you have places where you need to simply indicate what products are being offered (without providing the available options), you need to remember that you may have multiple rows for each product, and SELECT your data with an appropriate DISTINCT or GROUP BY included. (not a big issue, more of a reminder).
2. If the images associated with a product are the same for different attributes (and I don't see why you'd have one image based on ("small", "medium", "large") and a different one for ("black", "white")), then you're repeating the image information on every line for the product. That means storing extra data, and having to update extra data. That's bad enough if the info is a simple path to the image; if the image itself is in the DB, then this is horrible.
3. If you've truncated the columns to the ones that directly relate to the question, then there might be other data in shop_product as you describe it that would be repeated for every attribute the product has.
4. Finally, it would be a good idea to be able to refer to the product_attribute data easily, if only to confirm that all options offered by the shop are still offered by the supplier (for instance, if the supplier stopped supplying small t-shirts, then presumably the shop would want to take those off its list.

NOTE: In my answer, I will assume that each table has a column that's a unique ID for that table. If, instead, you have a composite primary key (multiple columns that, taken together, are unique to your table), you'll need to substitute that information for the "missing" ID I reference. If you have any tables with no primary key, I highly suggest you add one.

If any of the first three items would be a concern, I would recommend both a shop_product table as originally planned, and a shop_product_attributes table as mentioned in the comments. the second table would look something like:

CREATE TABLE shop_product_attributes (
       id int  -- something autoincrementing would work here
      ,shop_product_id int -- (FK to shop_products tables)
      ,product_attribute_id int -- (FK to product_attributes tables)
      ,options VARCHAR(1024)
);

Note that we're not copying the description from the product_attributes table. From the OP's information, I'd assume that the attribute name/description (like "size" or "color") is not something that the shop would need to change. However, the shop does need to be able to have different options, so we must provide a place to store those at the shop level.

Data types should match what you actually use in product_attributes. You might need to use shop_id and product_id instead of the ID from shop_products; it depends on what the actual primary key of that table is.

I included product_attribute_id to cover my fourth item above. With it available, it's much easier to confirm that the attributes the shop is offering are still available from the supplier.

NOTE: There is an alternative to storing a shop-level copy of each product attribute. If a shop was going to sell all the options under a given attribute, you could possibly save a lot of space by not create a shop-level copy of that product attribute. Instead, write your query against the shop-level attributes to also link to the original product attributes, and return the product-level options when there's no entry at the shop level.

EXAMPLE:

products table includes:

+----+-------------+-------+
| id | description | price |
+----+-------------+-------+
|  1 | T-Shirt     |    10 |
|  2 | Car         |   100 |
+----+-------------+-------+

product_attributes includes:

+----+------------+-------------+------------------------------+
| id | product_id | description |           options            |
+----+------------+-------------+------------------------------+
| 71 |          1 | size        | ["small", "medium", "large"] |
| 72 |          1 | color       | ["white", "black"]           |
+-----------------+-------------+------------------------------+

shop_products includes:

+------+---------+------------+-----------+
|  id  | shop_id | product_id |   image   |
+------+---------+------------+-----------+
|  393 |       1 |          3 | image.jpg |
|  394 |       1 |          1 | image.jpg |
|  395 |       2 |          1 | image.jpg |
|  396 |       2 |          2 | image.jpg |
+------+---------+------------+-----------+ 

Let's assume shop 1 wants to sell just small and medium t-shirts in black, while shop 2 wants to sell small, medium, and large, in black and white.

OPTION 1:

Store each shop's selected options for each attribute; shop_product_attributes shows these attributes for product 1 for shops 1 and 2:

+------+-----------------+----------------------+------------------------------+
|  id  | shop_product_id | product_attribute_id |            options           |
+------+-----------------+----------------------+------------------------------+
| 2491 |             394 |                   71 | ["small", "medium"]          |
| 2492 |             394 |                   72 | ["black"]                    |
| 3699 |             395 |                   71 | ["small", "medium", "large"] |
| 3700 |             395 |                   72 | ["white", "black"]           |
+------+-----------------+----------------------+------------------------------+ 

OPTION 2:

Store each shop's selected options for each attribute only if they do not use all options available; shop_product_attributes shows these attributes for product 1 for shops 1 and 2:

+------+-----------------+----------------------+------------------------------+
|  id  | shop_product_id | product_attribute_id |            options           |
+------+-----------------+----------------------+------------------------------+
| 2491 |             394 |                   71 | ["small", "medium"]          |
| 2492 |             394 |                   72 | ["black"]                    |
+------+-----------------+----------------------+------------------------------+ 

Since shop 2 sells all the options, no records are shown for it in shop_product_attributes. When retrieving the attributes for a shop's products in this case, you'd create a query JOINing shop_products and product_attributes on product_id, and LEFT JOINing shop_product_attributes to the other two tables on shop_products.id = shop_product_attributes.shop_products_id and product_attributes.id = shop_product_attributes.product_attributes_id. You'd pull in description from product_attributes, and pull in options as COALESCE(shop_product_attributes.options, product_attributes.options). This would return the options selected by the store if there were any, and the default product options if the store hadn't opted out of anything.

While selecting option 2 has the potential to save a lot of space, it has one possible drawback. What if the supplier of the t-shirts started making a new color available, "red" for example?

With option 1, no one is selling red t-shirts right away - they have to opt into the change before the new option is available from their shop.

With option 2, shops that have selected to opt out of certain options would not immediately be selling the red t-shirts; however, shops that had wanted all the options previously will automatically start selling the red t-shirts. At a minimum, this means the behavior is different based on whether you wanted all the options previously offered or not. Some shops might actually want to pick up new options if they're offered, sight unseen; others might demand the ability to confirm that they want a new option when offered.

From that perspective, I'd be inclined to recommend option 1, even though it eats up more space (but see my final note).

ASIDE: If a supplier stops offering an option, then with either of the above options, anyone who has the current options specified for an attribute would continue to show them as available (with option 2, users who took all the options for the attribute previously would automatically stop showing the removed option, since they'd be showing whatever the supplier showed).

You will need to either:

• create a utility process that can remove that option from all appropriate shop_product_attributes; or
• compare the options at the supplier level to the options at the shop level, and only show options that appear in both. I'd recommend this method if possible, as it would mean that an option that was gone for just two weeks (due to a issue at the supplier, for example), and then came back would automatically be picked back up by all shops.

A FINAL NOTE: I'm assuming that you have had some specific reason for putting multiple values in a single column (options under product_attributes and now shop_product_attributes). If you don't have a reason to have done that, I would overhaul the whole thing and go with a layout more like that proposed by Jon of All Trades; it's far more standard in my experience, and far easier to work with. Also, it allows for possibilities like a shop that wants to offer small, medium, and large black shirts, but only small white shirts, which the current scheme can't handle.

Answer 3

I think I would try this setup:

+----+-------------+-------+
| id | description | price |
+----+-------------+-------+
|  1 | T-Shirt     |    10 |
|  2 | Car         |   100 |
+----+-------------+-------+

+--------------+------------+-------------+------------+
| attribute_id | product_id | description | options    |
+--------------+------------+-------------+------------+
|            1 |          1 | size        | ["small"]  |
|            2 |          1 | size        | ["medium"] |    
|            3 |          1 | size        | ["Large"]  |    
|            4 |          1 | color       | ["white"]  |
+--------------+------------+-------------+------------+

+---------+--------------+------------+-----------+
| shop_id | attribute_id | product_id |   image   |
+---------+--------------+------------+-----------+
|       1 |            1 |          2 | image.jpg |
|       1 |            2 |          3 | image.jpg |
+---------+--------------+------------+-----------+

The two first tables remain "pure" master data. The 3rd table will only (almost, beside the image) be used as a link table.