I am struggling in resolving a db design for MySQL 5.7 to solve the following scenario:

There are category dimensions that change each day, I store them in category_entities. They belong to a category which relates to a merchant and his products:


merchant_id | merchant
1           | amazon
2           | ebay

PK: merchant_id


prod_id    | merchant_id | product
1          | 1           | jumper big
2          | 1           | hat red
3          | 2           | shoe black
3          | 1           | shoe black2 

PK: merchant_id, prod_id


cat_id     | merchant_id | category
1          | 1           | jumpers in green 
2          | 1           | hats for woman
3          | 1           | shoes
4          | 2           | jumpers 
5          | 2           | hats for children
6          | 2           | shoes

PK: cat_id

category_entities (30M rows)

cat_id | prod_id | characteristica | date
1      | 1       | 23              | 2021-01-07
2      | 2       | 22              | 2021-01-07
1      | 2       | 22              | 2021-01-08
2      | 3       | 01              | 2021-01-08
3      | 1       | 22              | 2021-01-08
4      | 2       | 01              | 2021-01-08

PK: cat_id, prod_id, date

So the category table defines what categories exists and the category_entities talbe shows the value for each day

How can I set a foreign key on the tables (categories, category_entities) on table products using prod_id, merchant_id. Those fields are the primary key of product.

I as reading about polymorphic assosiations but I am not sure this is one and how to solve this.

  • If a product is identified by prod_id, merchant_id you need that information in any dependent table, i.e. category_entities. However, your sample data indicates that prod_id alone would be sufficient, is the primary key in product correct? Would a new relation product_merchant solve your issue? – Lennart Jan 8 at 7:24
  • The sample data was missing a value, edited question. same prod_id other merchant. The table categories defines which categories exist per merchant while the category_entities logs which characteristica apear each day in those categories per product. My reasoning was that adding merchant_id to category_entities it would be duplicate data as it is already defined in categories. What happens if a product gets removed? I want to make sure that relations are clear not entities and categories exists without coresponding products/categories. That's why I am trying to solve it with fk. – merlin Jan 8 at 7:43
  • Can a product exist without a merchant? – Lennart Jan 8 at 7:45
  • No, thats impossible. The product table represents products found on e.g. amazon, ebay. – merlin Jan 8 at 7:47
  • No product I am familiar with allows an FK towards a partial key, so that means you will need merchant_id in category_entities – Lennart Jan 8 at 7:51

I'll add a partial answer since it is too much information to stuff in comments. I'm not saying that it is the best solution, but unless you want to remodel, it is a possability. Given the facts you presented you can extend category_entities as:

CREATE TABLE category_entities
( cat_id int not null primary key
, prod_id int not null
, merchant_id int not null
, ...
    constraint ... foreign key (prod_id, merchant_id)
                   references products (prod_id, merchant_id)
, ...

Now, you may have inconsistent merchant_id information between categories and category_entities. You can prevent this by adding a unique constraint in categories:

ALTER TABLE categories ADD CONSTRAINT ak1_categories
    UNIQUE (cat_id, merchant_id);

Now you can reference this constraint in category_entities as:

ALTER TABLE category_entities ADD CONSTRAINT fk1_categories
    FOREIGN KEY (cat_id, merchant_id)
    REFERENCES categories (cat_id, merchant_id);

This will guarantee consistency between category and category_entities, but is a bit ugly since ak1_categories is a reducible key.

I've heard rumours that there exists DBMS that allow CHECK constraints with sub-queries, but I never used one. Beside MySQL 5.7 doesn't care about check constraints so that is not an option for you.

Another alternative is to add before triggers for validation of merchant_id. They are however procedural by nature so they do not tell you anything about the current situation. All you know is that no invalid data passed them as long as they were active.

As mentioned this is not a complete answer but presents some ideas that do not fit in a comment.

  • Great! That seems to have solved the problem. I have a on delete cascade option set and this worked perfectly. Please allow me one more question: I have a PK set on cat_id in category. Now I added the unique index cat_id, merchant_id. Would it not be better to extend PK to merchant_id instead of adding another unique index? – merlin Jan 8 at 9:14
  • If you extend the primary key that will allow the same cat_id for multiple merchant_id. – Lennart Jan 8 at 10:27
  • True! Thank you for the follow up. – merlin Jan 8 at 10:29
  • Also, the constraint is implemented via a unique index, but the index is not part of the logical model where constraints reside. Not sure about MYSQL but for most DBMS you cant reference a unique index, you need the constraint. – Lennart Jan 8 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.