Database design 101

Question

I have a very basic question regarding the design of a database. I thought I knew the answer, but after seeing an online tutorial on a triple dropdown menu and the design of the demo database, I'm not so sure anymore.

Ok here's the setup, there are three tables involved.

First table: tblcountry Fields: country_id country

Second table: tblstate Fields: state_id country_id state

Third table: tblcity Fields: cidy_id state_id city

Now my question is: In table three, is it necessary to include the countryid as well? By using SQL you can query which country the city is in....or is it good design to have a reference to the country in the city table??

I hope someone can help :-)

Answer 1

Short answer, correct 99.9% of the time: No. You already have the reference to the country via the state record.

Long answer, for the 0.1% of the time: You may need to make a direct reference to the country from the city because you have a lot of queries for the cities of a country, and adding a second table to the query will make the query take much longer.

Granted this second scenario is not likely for record sets of cities, states, and countries, but for other types of data it is likely if you are managing a lot of records.

EDIT: In my job, I have a lot of these situations where I just keep extra fields in tables three or four levels of reference down. These are huge databases, and putting intermediate tables in all the common queries really stresses the database (much more memory used per query). so I would start with no direct references, and then add "shortcut" fields as needed. This gets into the issue of solving the scalability problem, and that is way beyond a beginner level problem. Also, if you add shortcut fields in downstream tables, then that is additional breaking point for your app (two records to update when you update a city to another country), and so keep that in mind as well.