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?
1
-
The question is too broad. Unless you give more details about your requirements it's going to be hard to answer it. It totally depends on your needs.– LaivNov 8, 2016 at 7:47
Add a comment
|
3 Answers
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.
-
I know that address change a lot. But city names and country names dont change a lot, dont they?– ReynaldiNov 11, 2016 at 6:55
-
Of course you are right, they are very stable. Everything is simple most of the time. But they are not stable all the time, caveats of border cases are still chasing us like 2 and 3 in my answer. Of course all these problems might not be important enough for you - we don't know, you have to decide it. But the very fact of their existence should be counted.– VladNov 11, 2016 at 7:06
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.
