I'm trying to design a database for my POS application and have stumbled when trying to create an address table. I'm confused whether to have city and country tables with predefined values and link the pk to the address table (city_code/country_code) OR just have city and country columns as string and ask user to enter manually. I tend to go with having city and country list, but i see that many other application does not use this approach and let user enter them manually. Can someone enlighten me?
For countries you can go for list of predefined values, they are changed very rarely.
For list of cities (and especially streets and houses) you better allow both. Most of the time users should be able to pick from existing (guaranteed to be valid) values, but in some border cases free input is required. Real life examples:
- Person is still registered at house which (with the whole street) was deleted from gis data since they were demolished ten years ago.
- Person has city/street name in a document written with a typo and you need data in the system to be a precise match.
- Your gis data is not accurate enough to have names of all villages in small province of some country in middle Asia.
- You've been given a contract to install equipment at some street which just has changed its name. However you can't enter the contract since gis data has not been updated yet.
Never allow the user to fill in these address fields absolutely freely. You will end with states Alobama, alabama, Alabama, Allabama, Albama and many others that you cannot even imagine, instead of the standard value. And it will need constantly a large amount of administrator work.
For some fields that change very rarely, or not at all, as continents or countries or inner states, use predefined fields. As for cities, allow users to fill in the values, but while filling, show them all values with the same start, for choosing.
Even that way has problems - if somebody will write Derlin instead of Berlin, it will be identified and proposed correctly. So, you should think about some administrator work - somebody should check new streets and towns from time to time, find for them the correct equivalent and the app should remember this connection for the future and use all of them for the propositions of the possible names.
And this is not the end yet. Imagine that somebody from nowaday Simferopol/Crimea wants to fill in his address. You put his area and town in the Ukraina list, but he knows that he lives in Russia and he will have to write it by hand and the application will start to look for Simferopol in Russia and will create it there as a new town. And you will evidence the same town as two separate places! Again, it needs some piece of administrator UI. It should be possible to join in one not only names of the cities, but the combination of city name + country.
If you are targeting a single country and have a good, flexible address verification API, you may be able to get away with a significant amount of prescriptive validation. The fact that you are thinking about using a country code implies that you are considering multi-country and multi-country address validation is a difficult problem to do well.
I remember a talk Rasmus Lerdorf gave at a Linux conference. Rasmus was born in Greenland, and Greenland is not listed as a country in a lot of lists (apparently) so he is of the habit of hacking the webpage, inserting the code for Greenland, selecting it and seeing if he can crash the application with valid data :).
I too have been a victim of poorly written address validation so I'd suggest using a prescriptive address verified page by default; but allow the users to do free-text entry of addresses on a separate page if their address doesn't "fit". That way the majority of your data is "good" and validated but you don't lose sales from customers with PO Boxes or in new subdivisions that haven't made it into the dataset yet or countries that you haven't bought address verification data for yet.