City County Country DB design question
Posted on 2006-04-17
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,