Populating a foreign key table with variable user input
- by Vincent
I'm working on a website that will be based on user contributed data,
submitted using a regular HTML form.
To simplify my question, let's say that there will be two fields in
the form: "User Name" and "Country" (this is just an example, not the
actual site).
There will be two tables in the database : "countries" and "users,"
with "users.country_id" being a foreign key to the "countries" table
(one-to-many).
The initial database will be empty. Users from all over the world will
submit their names and the countries they live in and eventually the
"countries" table will get filled out with all of the country names in
the world.
Since one country can have several alternative names, input like
Chile, Chili, Chilli will generate 3 different records in the
countries table, but in fact there is only one country.
When I search for records from Chile, Chili and Chilli will not be included.
So my question is - what would be the best way to deal with a
situation like this, with conditions such that the initial database is
empty, no other resources are available and everything is based on
user input?
How can I organize it in such way that Chile, Chili and Chilli would
be treated as one country, with minimum manual interference.
What are the best practices when it comes to normalizing user
submitted data and is there a scientific term for this? I'm sure this
is a common problem.
Again, I used country names just to simplify my question, it can be
anything that has possible different spellings.
