Mysql – Using a many-to-many table with a one-to-many relation

database-designMySQL

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?

Best Answer

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
)
Related Question