How do you handle the countries list and states/province list

[blackhorse]

I have a site which more than 95% sales are for usa and canada. less than 5% are for other countries.

In ZenCart, it only has a countries list (a country table list all the countries, and in the address table there is a countries_id to link to the certain item from the country table), but the states/province fields are free input text field in ZenCart.

But we may want to put the USA's states or Canadian Provinces into a "state-province table" and in the address table, use state_id to refer to the certain state or province from that "state-province table" like we did for the countries in ZenCart. This way, when we can apply the sales tax to the different states, or apply differnet shipping cost to different states. Regular ZenCart doesn't have it.

So my design is

"Address" table will have the following fields

...
countries_id
state_province_id
state_province_name


So if you are from USA or Canada, you will be assigned a state_province_id (bigger than 1 integer) which refer to an item in "state_province table", in this case, state_province_name will be empty.

But if you are from other countries, then you will be assinged state_province_id as 1, and in the "state_province table" id 1 means you are not either from USA or Canada. And you will get your state/province name from the state_province_name field of the address table.

OK, that is the database design.

For the member registration form when the fill out the address. There are two approaches

1) list the countries drop down menu before the state/province field. If the user select usa or canada, the state/province will be a drop down menu list all the states/provinces. if the use select other countries, the state/province will be a text field. (this way is simple, but ask people to select the country before the state/province is kind not so professional.)

2) or state/province will be a drop down menu, with all the states and provinces, but with one additional selection, "other countries". If the user select one state or one province, then that is all, the countries id will be also set up. If the user select "other countries" it will show a free text field, let them input the name of the province from other countries. And then they will also have to select one name from the countries drop down menu in the next step. This is complicated, but the user will select state/province first before the select countries, more like American way.

Which way you want to use? Or if you have other suggestions?

The largest stores such as officedepot, homedepot, futureshop etc. they have the branches in each countries. So their .com or .ca only deal with the USA or Canada orders, so it is easy that they don't have to worry about have the countries list like ZenCart has too, they only have the states list.

[blackhorse]

Any one? Many small to middle size online shops in USA or Canada will have to use a countries table like zencart for international orders. (Such as white list the countries they will do business with or black list the countries they will not do business with etc. Either way, we need a countries table in the database.)

But zencart general module doesn't have a states list. In zencart addressbook, state is a free input text. But for these small to middle size online shops in USA or Canada, they would also need to be able to apply control to the states and provinces, specially to calculate the tax. So a states table is needed in the database too for usa and canada orders. in "states" table can have a tax rate or tax flag on or off etc.

by the way, zencart general module doesn't have a states or provinces table, the state field is a free input text field in the address book. how zencart calculate tax on the shipping address for certain state/province at certain pre set up rates?

In simple words, in my case, I need countries table for all orders, i will use free text input field for the international orders (not in usa or canada), but for usa/canada orders i will get the state/province name from a states-province table.

Any one has experience to share? Zencart general module doesn't have the states table. But I believe that there are might be some modifications of zencart dealing with the exact situation I was talking about. How does it do it? I am not using zencart in my project. Just using zencart as the example to demo my question.

Thanks!

[susb8383]

In ZenCart, it only has a countries list (a country table list all the countries, and in the address table there is a countries_id to link to the certain item from the country table), but the states/province fields are free input text field in ZenCart.


I know the original post is 2 years old, but I just want to reply in case someone happens upon this thread.

What Blackhorse said above is not true; ZenCart does have a state table. It is called zones because it is really states, provinces, etc.

By default the state field is free form, but in the admin of the cart you can specify for it to be a dropdown based on the country selected.

[Roger Ramjet]

And you can get all the states/provinces/counties/regions for all the countries HERE (http://mic.loc.gov/archive_directory_1/country.htm)

Now, it is NOT 'unproffessional' to have the user select the country first, it is necessary and users will understand that when they see the second dropdown now filled with the correct list for their country. Indeed, users from other countries are offended when any website is just set up for USA adddress formats and that loses you business. I have been on sites that would not accept my UK post code (zip to yanks) even after selecting UK because the validation was just set up for us zip codes and uk post code are a different format - so they lost my business and that of every uk user.