Post reply

Design database to store countries, provinces and cities

  • December 11, 2008 at 5:43 am

    #203912

    I am developing an application (online) which is about FindAProperty.

    This is intended to be used in many countries, provinces and cities.

    Currently I am designing the database.

    What is the best way to store countries, provinces correspond to country and

    cities correspond to province and country.

    I have taken 3 tables for these 3 and created integrity constraints.

    And in the propertydetails table, Is it good to take these three fields

    (countryid, provinceid, cityid)?

    Please advice me the best way to design database for this.

    cheers

  • December 11, 2008 at 6:51 am

    #911034

    i have a similar GEO database;

    i have separate tables for countrytable ,province/region/state table , and city table.

    my address table points only to the citytblkey; i don't bother keeping the statetablekey or countrytblkey in the same row.

    The city tablein turn only points to the province table, which in turn has only one key to the country.

    I have views which join all the information so i can find all provinces for a given country, or all cities for a given country along with their province, but i don't repeat the data in the core tables...just in a view or two.

    I cna't say it's the right or better way, but that's what i've done on a similar issue.

    even above the country table, i have two different "continents tables", as it's handy to know which continent a country primarily resides in, depending on their culture...

    one content table is US style, which teaches there are 7 continents,and the other is how other cultures treat the world as 6 contents (5 really, since nnone lives in the antarctic)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 11, 2008 at 8:17 am

    #911113

    Hi,

    thanks for the reply. I got it.

  • December 11, 2008 at 8:22 am

    #911120

    I don't normally keep data down to the city table (I let the users have typos for city in addresses), but for countries and states/provinces I would only keep the state/province in the details table. And if you go to city only city like Lowell does. That's the whole point of normalization.

    Now if you are not doing many inserts/updates to the data and are mainly reporting on it, it would not hurt to have all 3.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply