City County Country DB design question

Posted on 2006-04-17
Medium Priority
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

star_trek earned 1000 total points
ID: 16473142
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

Assisted Solution

gabeso earned 1000 total points
ID: 16475970
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'.


Expert Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

615 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