CODEWITHSUNDEEP
mysqljoin
Anwar
Anwar

Reputation: 35

a many to many relation.. is it always better to do an intermediate table?

In MySQL, I have a table called "maintenance" and another called "technicians" where a maintenance can be done by one or many technicians... To do this, there is the commun way of creating a table Maintenance_Technician, containing records with the couple (mnt_id, tech_id).. than, to get data, we have to make a double inner join...

Here is the query (MT_Mnt_Id and MT_Tech_Id) are fields of the intermediate table "Maintenance_Technicians" SELECT * FROM Maintenance INNER JOIN Maintenance_Technicien ON Mnt_Id=MT_Mnt_Id INNER JOIN Technicians ON Tech_Id=MT_Tech_Id

I wonder if this is the best way to do it, if I have a maximum of 7 technicians to do each maintenance!

There are many other non commun ways such as : 1- Having 7 fields in the "maintenance" table, one for each technician, and letting null the unused fields (when less that 7 technicians make the intervention) in this case the select query would be : SELECT * FROM Maintenance INNER JOIN Technicians ON (Mnt_Tech1_Id=Tech_Id OR Mnt_Tech2_Id=Tech_Id OR Mnt_Tech3_Id=Tech_Id OR Mnt_Tech4_Id=Tech_Id etc..)

2- To store the ids of the technicians in one field, each one written between ';' for example ";5;10;25;". The select query would be like this : SELECT * FROM Maintenance INNER JOIN Technicians ON (instr(Mnt_Tech_Ids, concat(';', Tech_Id , ';')))

I like the second non commun way, but I am not sure if those are better than the commun way or not.. can you help me take decision? Is there any better solution?

Thanks in advance

Upvotes: 1

Views: 70

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562691

In general it is better to use the extra table to represent a many-to-many relationship.

The two alternatives you show violate First Normal Form. But there are practical problems too.

If you have 7 columns, it complicates many types of queries you would do against this data. For example, if you want to add a technician to a given maintenance. You would have to figure out which, if any, of the columns are "unoccupied". If you use a standard many-to-many table, all you need to do is INSERT the new technician.

The 7 columns method also means you can't set up constraints like UNIQUE or FOREIGN KEY. Also, if you someday need to allow a maintenance with 8 technicians, it becomes a hassle.

The problems with using a string of id's are many. I posted about this here: Is storing a delimited list in a database column really that bad?

The exception to using a normalized data design is when you have a fixed set of queries you ever run against the data, and you want to optimize those queries. Then you can use denormalization carefully for the sake of those certain queries.

But if you have a variety of queries you might run against the same data, or can't predict the future requirements, then a normalized data design is more flexible, while also giving you a lot of advantages of data integrity and query simplicity.

Upvotes: 1

workabyte
workabyte

Reputation: 3775

much better to have the linking table in my opinion. later you may want to add additional information that is specific to the link and then will have to re-engineer everything.

having the linking table will

  1. make it clear that there is a one to many
  2. let you enforce / validate relationships easily
  3. leave room for growth later

while you can store the values the way you want you will likely want to change that some time down the road. with you specific example i can see that there could be more information specific to the tech working on a task like the total time spent (just an example, likely want to store logs for that somewhere else)

hope this helps

Upvotes: 0

Related Questions