I have a table with a field named Schedule with data type varchar(20) that currently holds a comma separated list of up to six (6) two (2) character schedule codes. Theses codes range from [a1-a9]-[g1-g9]. I realize now that this is bad practice as it limits query performance and leans heavily on program code for data continuity. I don’t currently run any queries on this data, but I can see where it could become useful to do so.
What is the best option to replace this column? My first thought was to create a reference table with a foreign key constraint linking it to the subject of the schedule and each code as a column with tinyint/bool as the data type. However, that would be a table with over 60 columns which sounds like I might be stepping into another anti-pattern.
Is there a better solution than a reference table? Is there a better way to implement such a table? I have full control of the database and can implement whatever solution will provide the best performance.
Edit:
By ‘reference table’ I meant what @gordon-linoff described below. A table with 60+ static entries that would then be referenced via foreign key constraint by a third table linking schedules to their subjects.
I think this is a duplicate question as suggested however, the selected answer is a broad comment on data normalization without any concrete suggestion of a best practice and the rest of the answers are variations of ‘Yes this is bad practice’.
I know very little about database management. If a static table and a junction table are the best practice then I have my answer. I was just worried about a static table that will likely never change.
Advertisement
Answer
In general, this is implemented using a junction/association table and a schedule table.
The schedule table would have your current code and any relevant information about it. At a minimum, this would be a description, but you might have additional information as well.
The junction table would have a separate row for each code and row in the original table. It would have foreign key relationships to each of this tables.