CODEWITHSUNDEEP
mysqldatabasedatabase-design
Navid_pdp11
Navid_pdp11

Reputation: 4012

Is merging two many to many relation in one relation table better than seperated relations?

I have tree table which two of them have a many to many relation with third table. now I want to know if I merge this relations together and create one relation table for this relations is a bad practice or not? merge two many2many relation together

I think this design mede my relation and oprations easier to manage and easier understand. but I am worry about performance. I want to know which one way has better performance?

shared middle table which I add its picture or create a middle table for each relation?

Upvotes: 1

Views: 942

Answers (1)

dmfay
dmfay

Reputation: 2477

Complex junctions aren't bad in and of themselves. A row in a prescriptions table could well have doctor_id, patient_id, and pharmacy_id values. This works because a single prescription represents the confluence of doctor, patient, and pharmacy (plus some other information which could be factored out into still more related tables). The important part is that the prescription can only be expressed in terms of several foreign keys.

What you have is different. You're describing a case where a row in tbl_1 can be linked to tbl_2 or tbl_3 depending on the value of rel_type. Multiple connections are possible with multiple rel_types. This is bad, because you can't create an enforceable foreign key constraint on tbl_x_id. It may look simpler at first glance because there's only one junction table, but it's hiding a lot of complexity in ways that will hurt later. It'll perform worse, but the real issue is the lack of referential integrity.

Upvotes: 2

Related Questions