0

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.

enter image description here

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.

1

Plan A: Use SELECT DISTINCT word FROM ... when pulling out. That is, eliminate dups when fetching.

Plan B: Have a SET datatype 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.

| improve this answer | |
  • Plan A would probably be possible, but inefficient. Plans B and C seem to be logically more sound. If I were to go the route of plan C, what would my table look like? key | round | question_id ---------------------------------------- or am I missing something? – mike Nov 23 '16 at 7:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.