I have a basic database set up for a school spelling bee (the scenario may seem slightly silly, but I'm using it as an excuse to learn a little more about database design). The main tables contains the words, definition, and associated attributes as shown, partOfSpeech and level being linked to two other tables.
The way that the spelling bee is structured is that each level contains a subset of the words from levels below it, and at the moment the words are entered in the database as they appear on paper, so certain words are occurring two or three times – the only thing that has changed for that row is the value of 'level'. For example, above, the word 'impostor' occurs three times, once for level 3, once for level 6 and once for level 7.
My question is how better structure my table to remove this duplication? Effectively I want the word, definition, and other fixed attributes in the table once, with the levels 3, 6 and 7 associated with it. In my head at least, it would also make it easier for me to not pull the same word twice from the database, if I choose to code the front-end not to.
Any help would be appreciated.
Best Answer
Plan A: Use
SELECT DISTINCT word FROM ...when pulling out. That is, eliminate dups when fetching.Plan B: Have a
SETdatatype to list 3,6,7.Plan C: Use an extra many:many table to map "imposter" to each of the 3 levels. There would be 3 rows for that word in the new table, but only one row in the current table.
Plan C may be the "proper" way to do it.