How to set foreign key on two tables in MySQL?

Question

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:

merchants

merchant_id | merchant
1           | amazon
2           | ebay

PK: merchant_id

products

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

categories

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.

Answer 1

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.