Database design question - 1 table or 4

TodManPlaa

Hi,

I have a question relating to the design of a database.

I'm working on a travel site that will contain information about a range on countries. Now what I'm curious about is what the best way is to arrange the static geographic information.

Sample information would be something like:
Country: Thailand
Region: Southern Thailand
Province: Satun
Location: Ko Lipe

Now my question is - is the above info better in one table eg:
cID|countryName|regionName|provinceName|locationName

or in four separate tables that would be linked by foreign keys to the parent table eg:

cID|countryName
rID|cID|regionName
pID|rID|provinceName
lID|pID|locationName

Or in four tables linked all the way through eg:

cID|countryName
rID|cID|regionName
pID|rID|cID|provinceName
lID|pID|rID|cID|locationName

It is very unlikely that any of this information would ever change, so what I'm looking for is a result that will work best from a performance point of view. Also, given the fact that this information would not change - would I be better, in the location table for example, to enter the real values so I don't need to do a lookup for the names that correspond to the ID - something like:

etc (but still keep the other tables to facilitate ease of data inputting (to populate dropdowns etc)

Any pointers suggestions would be much appreciated.

Thanks

laserlight

If you use only 1 table, then you would have many duplicate values, since given 10 provinces in a country, that country name would be repeated 10 times. While the info isnt likely to change, I still think that keeping different table would be better, or at least take up less storage space.

Maybe for your case, the best is "four tables linked all the way through"

superwormy

I would go with one of these two:

cID|countryName
rID|cID|regionName
pID|rID|provinceName
lID|pID|locationName

cID|countryName
rID|cID|regionName
pID|rID|cID|provinceName
lID|pID|rID|cID|locationName

I've delt with both before, from my experience, the first is much easier to update and work with on the programming end, although eithre might work.

Depending on the queries... the second one might offer better perfomance. But perfomance most of the time is mute, you'll most likely be able to easily generate pages faster than anyone can download them anyway with almost no load on the database...