1

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.

resident0
  • 35
  • 5
  • See the many-to-many option here: https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de/7296873#7296873 – NullUserException Sep 30 '19 at 23:10
  • Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Sep 30 '19 at 23:10
  • @philipxy, My old answer in that question explains the downsides of using a delimited list, but not what to do instead. – Bill Karwin Oct 01 '19 at 01:38
  • @BillKarwin There are a zillion duplicate askings & answerings of this. @ resident0 Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. We cannot reason, communicate or search unless we make the effort to (re-re-re-)write clearly. – philipxy Oct 01 '19 at 02:56
  • Time to follow a published academic textbook on information modelling, the relational model & DB design & querying. (Manuals for languages & tools to record & use designs are not such textbooks.) (Nor are wiki articles or web posts.) Dozens of published academic information modeling & DB design textbooks are online free in pdf. stanford.edu has a free online course. (But asking for resources outside SO is off-topic.) – philipxy Oct 01 '19 at 02:58
  • 1
    @resident0 Apologies for being self-promoting, but I have written a book called [SQL Antipatterns: Avoiding the Pitfalls of Database Programming](https://pragprog.com/book/bksqla/sql-antipatterns) which might be interesting to you. I wrote it to explain common mistakes _and_ their solutions, and to be much more approachable than an academic textbook. :-) – Bill Karwin Oct 01 '19 at 03:09
  • @philipxy I'm not looking to delve deep into to topic of data management. I just wanted to improve a personal project by eliminating what I suspected and had confirmed to be a bad practice. – resident0 Oct 02 '19 at 14:16

1 Answers1

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This is what I was thinking of. I was just concerned about adding a static table. But as @NullUserException linked in comments above, and you describe here, the many-to-many approach seems most likely the best option. – resident0 Oct 01 '19 at 00:07