0

I am new to stakoverflow. I have research a lot and I don't think I've found the information I needed. I hope I am not asking something that's been covered already or mixing questions.

I need to collect customer address that service all the world. I looked into few feeds already they helped me a lot but I still need advice. Initially I was going to create a customer table (id, customer_account, name, email) Address table that will contain (id, fk_customer_id,address line1, line2, city,fk_province-id, fk_country_id)

My dilemma is coming from the differences in address structure the world actually have. SO I wanted to ask some specific design and normalization questions.

I am thinking this is what I want customer table (id, customer_account, name, email, country) the country can be used later to improve formatted output in case such thing will be needed in the future (counting on expansion)

as far as the addresses go I have few options that I think I want to consider but not sure which one is best. (all options will use same customer table structure)

1) use a US driven address tables and a none US driven tables

US table will look like US_address(address_id, fk_customer_id,line1,line2,city,fk_state_id,zipcode,fk_type_id)

address table2 (addres_id,fk_customer_id,line1,line2,line3,line4,city,postal,fk_type_id)

the country id will be the deciding factor onto which table I will select or join into US or none US. Note:type_id will tell me if it was a shipping address or billing address or even maybe home, business just as an example

2) use one address table but add a child table to represent lines I like this a lot but it requires join (I don't think join is a bad thing (please correct me if I was wrong) and it will also require the insert to be in a transaction that will insert data to 3 tables like this customer --> get id and insert into address --> get id and insert into lines

design looking like this: address (addres_id,city,province/state, postal_code,address_type) address_lines(address_id,address_line,line_number) this will allow me to have vertical representation of address line vs horizontal representation so instead of having (addres_id,fk_customer_id,line1,line2,line3,line4,city,postal,fk_type_id) with most cases line2, 3 and 4 will be empty, I will add an address line as needed and the join will return all lines for a specific address_id which can give 1,2 or 4 lines

I like 2 and your input is greatly appreciated. I wanted to know what you think of structure, performance, normalization and design ...etc

Also if I pick option 2 is this normalized enough or do I need further normalization. ie the province might have a lot of redundant data should it also be a table?

++++++++++++++++++++++++++ UPDATE ++++++++++++++++++++++++++

The database will serve online users. I don't want to see if I should break out the address into its own table. My main concern is having a solution that is universal for the world. Address format changes from one country to the other one so I wanted a simple solution that is complex enough but not too complicated as I would like it user-friendly too. I would like to break it all up into so many fields but I don't think that solution will make it easy for online forms. I would like input on my current options 1 or 2. the reason I am adding line1, line2, line 3 is to simplify the user interface and while I still serve international users. Users might have to supply floor number or building number but yet I don't have to specify that or ask for specific information. (I am taking into consideration what's popular on the web for online data collection and form formats.)

S. Alsaraby
  • 1
  • 3
  • Possible duplicate of [Is it worth breaking out address information into a separate database table?](http://stackoverflow.com/questions/17226201/is-it-worth-breaking-out-address-information-into-a-separate-database-table) – Joel Brown Feb 18 '16 at 23:55
  • Have a look at this question: http://stackoverflow.com/questions/17226201/is-it-worth-breaking-out-address-information-into-a-separate-database-table/17233857#17233857 I think it addresses your concerns. – Joel Brown Feb 18 '16 at 23:55
  • Thanks for your input but that question did not answer mine. I am breaking up the address into a sperate table already but I wanted to see if I should break up the address table further more. state and city might be already into another table so my main concern is since I am dealing with international addresses which will be different than USA ones how can I have a generic solution that fit both without losing performance? Would it be best to have option1 (2 tables for address) or option2 one table different format? – S. Alsaraby Feb 21 '16 at 03:06
  • My point is that you are already going down the wrong path. Your question is, in my opinion: "Should I go half way down the wrong path or all the way down the wrong path?" As you've already noticed, address formats vary a lot around the world. Shoehorning them into one structure will always imply inconveniencing one locality or another. – Joel Brown Feb 21 '16 at 12:30
  • Also, you say you want to "make it easy for online forms". What is easier than a single multiline textbox? There are tools that parse, standardize and format addresses. By one of these to break addresses out into parts when (and if) you need that. Give your users a break and let them enter their addresses in a format they like. I can tell you from years of experience that they're going to do that anyway, regardless of how you try to force them to parse their address by using separate fields on an input form. – Joel Brown Feb 21 '16 at 12:32
  • Thank you Joel, Why do you think a multi-line text box (textarea in html) is better/easier than multi-single line textbox. Isn't it possible to have errors in breaking up the address once it is passed as one string for address, city, state ..etc. I This is a tough subject and not having a standardized way makes it a little more difficult to handle. It is doable I don't know how much time I should spend on this. I was checking DHL site since they do international shipping they implemented 3 single line textboxes for customer registration in certain countries – S. Alsaraby Feb 22 '16 at 17:09
  • In my experience, users do not read/follow forms. Also everyone has an address and even within a single country people will have many different ideas of how they like to capture address information. In many places there are even multiple "correct" ways of writing any single address. If you are relying on the user entering the address data to put things in the right place with a consistent, correct format then you are doomed to have low quality data. There are highly specialized tools for doing address correction and standardization. Use one of these if you want your addresses to be good. – Joel Brown Feb 22 '16 at 17:15
  • There are several videos that I put together for a former client of mine. I'm not recommending their product (nor am I recommending against it). I only point this out because there is useful general information about address data quality embedded in the product information videos that you will find here: https://www.youtube.com/user/ncodetv – Joel Brown Feb 22 '16 at 17:18

1 Answers1

0

I think you should have a normalized address table like:

CREATE TABLE [dbo].[Address](
    [Address_Line1] [nvarchar](150) NOT NULL,
    [Address_Line2] [nvarchar](50) NOT NULL,
    [City] [nvarchar](50) NULL,
    [Province] [int] NOT NULL,
    [Country] [int] NOT NULL,
    [Postal_Code] [nvarchar](10) NOT NULL,
    [Type] [char](1) NOT NULL,
    [Room] [varchar](50) NULL,
    [Floor] [varchar](50) NULL,
    [Cross_Street] [varchar](255) NULL,
    [Street_No] [varchar](20) NULL,
    [Street_Suffix] [varchar](10) NULL,
    [Unit_Suit_Appt] [varchar](50) NULL,
    [Street_Name] [varchar](255) NULL,
    [Street_Type] [varchar](50) NULL,
    [Street_Direction] [varchar](20) NULL,
    [PoBox] [varchar](50) NULL,
)

where Province and Country are FK to Province and Country tables which are populated beforehand. Feel free to use/not use additional fields depending on your needs.

pdube
  • 593
  • 1
  • 11
  • 26