1

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

Anwar
  • 35
  • 5
  • 2
    What happens when you add an 8th tech? or you remove a tech? linking table is likely the best approach. Keep in mind that you can store information specific to each tech/maintenance connection in the linking table too, for example a description of what each tech did, or how long each tech worked on the job, when each one showed up or departed, etc. – Kevin B May 27 '14 at 18:11
  • To expand on what @KevinB is saying, if you change the number of technicians, you will have to change the schema. This is much worse than changing application code. Your second suggestion is an even worse example of the first, in that it actually violates the atomicity principle. – outis nihil May 27 '14 at 18:12
  • Yes, for the first proposition I know its limitation, that why I said that I like the second one... – Anwar May 28 '14 at 11:01

2 Answers2

1

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.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you very much all for your reply. Bill Karwin, thank you for your detailed answer, it illuminated my mind :) – Anwar May 28 '14 at 10:58
  • In fact, my aim is to minimize the load on the processor of the server by reducing the number of tables in every SELECT query.. So I couldn't decide which method is much easier for the process to accomplish – Anwar May 28 '14 at 11:07
  • The number of tables isn't the bottleneck, it's the number of rows from *any* table. Storing denormalized data is great for a few specific queries that it helps, but it increases the cost of *other* queries by orders of magnitude. Storing in a normalized fashion helps the most broad variety of queries. – Bill Karwin May 28 '14 at 16:04
0

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

workabyte
  • 3,496
  • 2
  • 27
  • 35