MySQL table creation methodology for repeated values


I want to ask you about what is most effective in a particular case. For example, I have these tables:

The turn values would be "TOMORROW, EVENING AND NIGHT" my question goes in this sense, it is necessary to create a separate table (as it is now) to store only those 3 values and integrate them as a foreign key, instead of repeating them in the table "corrective_work" as a varchar? (I'm not interested in being enum)

Thank you.

asked by German Avalos 04.08.2018 в 04:58

1 answer


I will mention only three important and demonstrable reasons why you should have a separate table, as you have it now:

  • Integrity / Maintenance / Error handling : this reason is enough to have a separate table. If you handle the data by repeating it again and again you expose yourself to thousands or millions of possible errors such as: TRDE , TARD , TARDF , MANANA , MÑANA , NOCHHE ... and keep counting. None of these erroneous data will appear when you make queries as WHERE turno = 'TARDE' . In any system the most dangerous virus to control is the user when he grabs a keyboard ... and our generation is characterized by spelling errors. I see at least one almost daily in the only thing I see on TV (the news or news as they say in America) and I speak of a developed country: Spain.

  • Flexibility : having a separate table will support any evolution of the data model with less traumas. Imagine that later a task can be done in several shifts. It would be necessary to create a new table called for example tareas_turnos , move to that new table the id_tarea and the id_turno of each row and from there continue to maintain the new data model without too many complications.

  • Space : Suppose it is a table intended to store millions of rows. It is not the same to have millions of VARCHAR than millions of TINYINT or millions of CHAR(1) .

  • Maybe there are more reasons, but I think these three are enough to see clearly that it is convenient to have a separate table.

    answered by 04.08.2018 / 07:31