Storing region / city data in User table
Suppose I have the following:
Table region_city
id name parent_id
==============================
1 North null
2 South 开发者_StackOverflow中文版null
3 Manchester 1
4 London 2
In my user table I store the ID of the City that the user is in.
Now in my search form I need to be able to perform a top-level search, i.e. find all Users that belong to a given region (North or South).
Will it make life easier if I included a region_id field in my user table? Or is that going against the normalisation concept?
It does denormalize the table structures and it could introduce data update anomalies. Consider: the user moves from Manchester to London and the city_id changes. The region_id could still point to the North.
The region_id only depends on the city therefore it does not belong in the user table. Since it can be derived from the city.
If the design absolutely calls for only two levels (region and city) and you are willing to forgo the possible addition of other levels in the future (not a decision I would be inclined to make, but you know your data better than I do) then do not include the regionID in your user table; that would denormalize your database. Instead, you have several choices for representing the data (including two related tables, region and city) and you would perform your search by JOINing the city table to the user table or using an IN clause in your search.
精彩评论