City County Country DB design question

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)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gabesoSolution ArchitectCommented:
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'.

I agree with gabeso.
I add : you should create a view which hide the complexity of joins and make easier handling requests.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.