storing school names in a database schema

Question

I'm designing a schema for an event registration system which will involve students from schools across many different regions. My main problem is the method by which I store school names in the database.

Given that students will be registering separately, it's highly likely that spelling variations of the same school name will accumulate over time. I'd like an easy way to purge these, especially as one of the statistics we'd like to gather would be the number of schools and institutions that register for the event.

I'm debating between storing school_name as an extra column in a Participant table, or storing a school_id as a foreign key referencing a School table (can't think of any other way). Which one would prove more efficient when it comes to utilization of storage, ease of purging duplicate data, and other factors?

Answer 1

Storing an ID as a foreign key to a School table is preferable since it leads to less duplication.

Answer 2

If you want to avoid the possibility of mistakes, you could provide a list (yes, it might be large) of existing school names. If they choose one, you store the ID of that school. Because you might not be able to anticipate ALL schools, you could have a free-form text field for "other" schools and store that as text on the participant record. You may have to do periodic reconciliation to add new schools to the school list, or link a participant's "other" school to an existing school (maybe it was in the list but they just didn't see it or maybe they misspelt it).

Answer 3

"Which one would prove more efficient when it comes to utilization of storage, ease of purging duplicate data, and other factors?"

Storage is cheap, time is not. So choose the approach which will minimize the amount of time you spend on de-duping the data and save your users some typing by providing a pre-prepared list of schools, and enforce the foreign key.

Of course, this means you have to obtain such a list, and provide a mechanism for searching it, which is inevitably more work than than giving your users a free text box. Nothing is every easy :) But free text is the enemy of standardization so avoid it as whenever possible.

---

According to your profile you are located in Singapore. If that is where your application is based you may find this Wikipedia page saves you a lot of typing (usual caveats about Wikipedia apply). Lists of schoold from many other countries are also available. Find out more.

---

'do "autocomplete" fields serve as a flexible replacement for drop-downs? '

They can do. But they can be completely irritating, especially if they make it hard to guarantee we have chosen the value we meant to choose. I find the SO tag feature is confusing in this regard, and I'm a technical type. Dropdowns are easier to understand. On the other hand, most young people are completely comfortable with predictive texting, so perhaps auto-completion would be more suited to them.

Answer 4

School names do change over time. Having lived through such things happening, it makes a lot of sense to make the names not be primary keys, i.e., you've got a table mapping them to an ID for each school which doesn't ever change for a school. The rest of the database can then use the ID and you can have proper foreign key relationships set up; the full kaboodle. You will want to constrain the name column to be unique; even if schools change names, no two will ever have the same name because they function in a very PK-like way in real life over a multi-month timescale (it's only on a multi-year timescale that you have to deal with changes).