A Basic Database Design Question

[rakelbara]

Hi 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: tblcountryFields: country_id country Second table: tblstateFields: state_id country_id state Third table: tblcityFields: 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 :-)

[justsomeguy]

You want to avoid duplicating data, so it's best to leave the country out. You would only add the country to the cities table if you want to list all tables in a country and that search is taking too long in the database.

[davej]

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 understand there is a city named Guilford in Connecticut, Indiana, Maine, Maryland, New York, Vermont, and North Carolina, and the state of New Jersey has two cities named Springfield.