4

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 ...


2

You could solve this with a two-column foreign key and cascading update, but that requires a redundant two-column unique constraint on report, which will harm DML performance and use extra storage space. The proper solution is to remove the redundant column from report_years. This concept of removing redundancy from a data model to improve consistency is so ...


2

You should choose a pattern for the FK name that respects the relationships between the tables. My preference is FK_<child entity>_<verb phrase>_<parent entity>. So fk_product_modified_by_user, fk_category_modified_by_user, etc.


1

MariadDB seems to handle it correctly. According to Fiddle the information_schema is correct, so the problem seems to be with your tools: SELECT table_name, constraint_name, referenced_table_name, unique_constraint_name FROM information_schema.referential_constraints; table_name constraint_name referenced_table_name ...


1

Column < Row < Block < Table One column had a number, date, string, or a small number of other possibilities. It takes a few bytes or many bytes. One row like you described (4 columns each taking a few bytes) will take perhaps 40 bytes when you add in some overhead. One block is 16KB and holds (in your case) a few hundred rows. I mention a block ...


1

Enforcing relationship between entities with foreign key constraints is [almost] always the right thing to do. So you're absolute correct about adding foreign keys. Naming itself is very subjective topic; in my opinion , consistency in naming DB objects is the most import thing. If you're working on existing project, maybe it is not a bad idea to ask DBA ...


1

From a data integrity point of view the transitive foreign key is redundant. And in principle, with a well written database management system, it wouldn't make any difference to performance, since the DBMS could see that the transitive FK is implied by the other two, so would not need to check it separately. Sadly this is not the case in the real world. I ...


Only top voted, non community-wiki answers of a minimum length are eligible