City County Country DB design question

Posted on 2006-04-17
Last Modified: 2012-06-27
Hi there,

I’m looking for the best way to implement the following:

Say I have the following table (the data is just to illustrate the problem)-


clientID      clientName      clientAddressLine1      cityID            countyID      countryID
1              paper ltd          34 test road              birm            westMid            eng      
2            cup ltd            52 test street            birm            westMid            eng
3            diary ltd          7 test lane                edin              east                    sco

I want the users to be able to find a client either by their city, county, or country. Should I leave the table as it is or should the city, county, and country be taken out of this table and related in some way?

I mean “westMid” is always going to be in “eng”, and birm is always going to be in “westMid”. Would like to know the proper way of doing this in regard to the DB design?

Currently my sql statement would be pretty simple e.g.

select * tblClients where countyID = “westMid”

Any help would be much appreciated,

Reiss :o)
Question by:reiss20
    LVL 11

    Accepted Solution

    If you are searching by city, county or country. It is better to leave everything in one table rather than referring multiple tables. But index your table based on possible searches.
    seraching from a single indexed table should be faster
    LVL 9

    Assisted Solution

    It depends on the size of the database.

    If you were required to index all homes in the country then you would construct a highly detailed postal address database using other information such as postcode as key etc.

    In the UK the postcode determines the street address. So a UK address can be shown as 'SE153FG 17'.

    You might want to encode these if the data is large: WM - West Midlands etc.

    In any data storage/retrieval situation you will have the codes for things and the actual descriptive (or display) text and these should be kept separate because, when searching for information, you need to map search terms (for example 'westMid') to the codes (or key values) for the records you want retrieved.

    You mention the condition "= 'westMid' " which means you have already encoded the location as the constant 'westMid' manually: Where is the actual address stored i.e. where does 'westMid' expand to 'West Midlands' for reporting?

    What you might want to do is separate the addresses to an address table and link tblClients to tblAddress by an internal id. You can then build alternative query structures on the address table (i.e. other links to other search tables): This is especially important if clients can share addresses.

    But only if the database is large. If you are talking about a few thousand records then 'star_trek' is right and a single table 'will do'.

    LVL 5

    Expert Comment

    I agree with gabeso.
    I add : you should create a view which hide the complexity of joins and make easier handling requests.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now