New answers tagged referential-integrity
With the clarifications in your comments, that there are only 2 levels of products, e.g. only Products and SubProducts and no subproduct is related to two or more products, your design is fine. I would only add two unique constraints on table SubProducts, on (IDproduct, IDsubProduct) and on (IDproduct, Name) - or make one of them PRIMARY KEY and the other ...
A hierarchical relationship is probably fine: PRODUCT id name parent_id (nullable) insert into product values (1, 'Proc A'); insert into product values (2, 'SubProA1', 1); For performance reasons, you will need special queries. I'd use postgres over mysql if I were you. Use WITH queries.
You can add Table4.id4, then you can replace Table1.id2 , Table1.id3 with Table1.id4 which references Table4. Or you can enforce constraint by creating indexed view.
There's a way to enforce that Table1.id3 only can seted to values in Table4.id3 where Table4.id2 = Table1.id2? It seems to me like you could just set a foreign key constraint directly to Table4. alter table Table1 add constraint your_constraint_name foreign key (id2, id3) references Table4 (id2, id3); You might have to jump through a hoop or two to ...
Top 50 recent answers are included