I have the following Customers table:

customer_id - int 
company_name - nvarchar
street - nvarchar
city - nvarchar
comments - nvarchar

The app will only be used in part of one small country (something like 30 cities). A friend told me I should seperate 'city' into a different table 'Cities' and use only city_id in customers table.

Personally I didn't see much of a benefit from it (except for saving some space on Customers table which seems insignificant to me in this case for the cost of creating another table).

He also mentioned that because I have duplicate columns - city: foo, city: bar, city: foo. (few customers in the same city) this is not considered normalized, is this true?

Who's right? Any enlightenment on the issue?

  • With that design you cannot have cities in your dropdown where no customer is. Aug 19 '12 at 21:38
  • 1
    There is no need to normalize address data in a sales database. If you were building a geospatial database, then there would be some point in normalizing. In a customer database, you would just be adding constraints that would be in the way more than they would be helpful. You are much better off storing the address as one or more text fields and using an address standardization tool to tidy the address up/validate it (if a validation tool exists for the country of interest).
    – Joel Brown
    Aug 20 '12 at 12:47
  • Are you ever going to need to apply taxes? In the US at least, taxes can apply at the city, county, state, federal levels. It can be advantageous to abstract cities etc into "locations" so that you can apply tax by location. HTH Sep 6 '12 at 16:50
  • Please remember that cities, counties, and zip codes are all SIBLINGS (children of State). It is not a hierarchical relationship (Manhattan contains counties whereas most counties contain cities. Some zips contain cities and most cities contains zips) Sep 6 '12 at 16:51

The design doesn't meet third normal form, but not just because of the city. The fields STREET, CITY are functionally dependant on each other (if you change the city, the street should probably change as well and vice-versa). You could also have the same street, city combination represented in different ways (Foo St, Foo; Foo Street, Foo; etc.).

To normalise this you would create a new table ADDRESSES which has the street, city etc. in and link the customer to that via an address id. This would also allow you to list several addresses for a customer (via a link table) if this is what you require.

This still leaves you to decide whether to extract the city into it's own table. To fully meet 3NF you should create a cities table, whether you need or want to depends on the answer to the following questions:

  1. Do you (or will you) have additional attributes of the city (e.g. population, county/state, display name etc.)
  2. Are you executing a query to generate a list of distinct cities to pick from (your comment suggests yes)

If the first point is true, then you should definitely create a CITIES table, otherwise you could end up with one city having different populations etc. If the second is true then it's a very good idea to have a separate table as the query to list all your cities will scale much better - you'll only have to scan this table rather than the (almost certainly larger) CUSTOMERS table and then get the distinct cities from that.

  • 1
    Perhaps you can explain why having street, city in a table violates 3NF; I don't think it does. Even if it did, moving both columns to a new table would then make the new table violate 3NF.
    – Andomar
    Aug 19 '12 at 18:29
  • 1
    If a customer moves to a different city, you're (almost) certain to have a new street as well, so there is a dependency between the two columns. Several customers could be in the same street and city, so (street, city) can't be unique in the customers table. By placing these in a separate table you can create a unique constraint on (street, city), ensuring these are functionally dependent on the primary key. Aug 19 '12 at 19:43
  • 1.Should I really go that far? Could you please give an example something 'bad' happening if street is in Customers table? Is normalization for address field crucial? (from what I understood normalization is not a commandment but should be used when you see an actual benefit)? 2.How come number 2 in your answer 'generating a query to distinct' has anything to do with the 3NF (which says that all fields will be dependant only on the pk)?
    – BornToCode
    Aug 19 '12 at 19:48
  • 1
    @BornToCode - you don't have to meet 3NF, it's entirely up to you. This has some good reasons for separating addresses. Point 2 isn't really to do with normalization, but performance. Scanning your list of 30 cities is going to be faster and scale better than having to generate a distinct list from the (presumably) many more customers you'll have (which could reach into the thousands) Aug 20 '12 at 11:32
  • 1
    That's what I mean with the disaster of overnormalization. You're introducing mad complexity for updating a customer that has no practical benefit at all
    – Andomar
    Aug 20 '12 at 17:28

When you get into it -- really get into it -- storing componentized address data is an extremely complicated problem because of all the disparate and varied systems in use globally.

I think whatever you develop needs to be balanced between flexibility, and storing only what your business needs to store.

The biggest piece of the puzzle here is to move all address-related fields out of the Customers table -- addresses are entities unto themselves.

The space cost may not be relevant in a very small system (maybe), but this is more about a technical debt issue. If you need to start adding more address-related fields, you would have to keep adding more and more of them to the Customers table. Sooner or later, you'll realize that this is an inflexible design -- if you need to use multiple addresses for a given customer (billing & shipping addresses being the classic example), now you're in a world of hurt without normalizing, as you can't reuse the existing structure to store the required data.

At an absolute minimum, create a new table Addresses, and then reference address_id from Customers. If you want to go the multiple address route eventually, even doing just this step (as opposed to sticking with the current design) will save a massive headache later.

The address line could go in either the Addresses table directly for simplicity, or in a separate Address_Lines table to handle multiple lines. (The latter is usually preferred.)

After that, a general minimum for being able to slice and dice your data in a meaningful way is to construct normalized Countries, Regions (aka provinces/territories/etc.), and Cities tables, with only the latter appearing as a field in the Addresses table. This lets you ask business questions like "how many products did we sell in city X?" and "how many products did we sell in region Y?". (Note: depending on where you operate, what data you have, and how the data will be sliced, you may require a 4th table in there between Regions and Cities.)

If you need to get more granular ("how many products did we sell to customers on street X?"), then you'll have to start componentizing the address lines themselves, which is the really difficult part. Usually, though, a business won't ask this kind of question. Given that I don't even see a postal code field, I'm guessing this is not something you care about.

  • 1
    This is a great answer. Being able to handle multiple addresses per customer is a powerful argument for normalization.
    – JNK
    Aug 20 '12 at 18:56
  • I agree 100% that it is madness to keep address in the customer table. I disagree with projecting out cities, provinces and countries. I've worked directly in the address data quality tool space for over 15 years and the problem domain is too complicated to solve well unless you happen to be focused exclusively on it. There are too many variations within a single jurisdiction, never mind across multiple jurisdictions. Keep addresses as text and use a specialized tool to ensure quality. For spatial analysis build up your own territories based on a low level attribute like postal code.
    – Joel Brown
    Aug 21 '12 at 15:01
  • 1
    @Joel: Interesting point of view; consider adding your own answer? I agree it's a very complex problem domain. I've always tended to try to separate out the required components as much as possible, so they can be manipulated individually (i.e., for either correctness of input, or for output). Using a special tool to do data normalization is a good idea, because a 3rd party has certainly solved the problem better than I ever care to; however, that type of thing usually costs $$$, which can be a significant barrier to entry.
    – Jon Seigel
    Aug 21 '12 at 16:02

Address modeling is not universal. A universal implementation would be too complex for most applications. The different models vary according to model type (OLTP vs OLAP), country rules, customer type (organization vs. individual), how critical the address data is, etc.

As said, you should separate city. Separating City will make the problem of having different city names in the customer table go away. Reasons are:

1 - Separating City names in a separate table allows you to run queries like: give me customer distribution by city and show cities where no customers are there.

2 - Allows you GUI to always refer to the correct list of cities.

3 - Allows you to maintain city information without touching your program code.

4 - Allows duplicate city names (in different states) if you use the diagram below.

If you are sure that your application is for 1 country, then don't add the country in.

Also, I noticed you don't separate the street address information into 2 columns which is common in North America and that you have no Zip Code. Review the country's postal address requirements from postal authorities to make sure your design conforms to them.

Here is a common representation of address in an OLTP application. Here the PK of City is 2 columns, namely, CityID and StateID. A variation on this version would be to use single ID for each table (as a sequence number) and end up with a FK composed of 1 column only at the customer table.

It all boils down to business rules and requirements.

enter image description here

The following is a basic representation.

  • Good luck writing the SQL to insert a new customer. -1
    – Andomar
    Aug 20 '12 at 12:35
  • 2
    You neglect to point out the disadvantages, namely adding the maintenance of the list of cities to a sales application and complicating the capture of address data. What happens when a customer can't find the city they live in on your web page? Does that mean you won't sell to them? I've worked with address validation software for over 15 years and I would say it is not worthwhile to attempt to maintain a comprehensive list of cities in an application that is focussed on sales, not geography.
    – Joel Brown
    Aug 20 '12 at 12:53
  • 1
    @JoelBrown, thanks for your comment. Requirements are the driver. I did mention the importance of requirements. Maybe this company have 10 store and opens only 1 store every 5 years. I can't tell from the given description. If you let cities be entered as a text you will get a mess, that is for sure.
    – NoChance
    Aug 20 '12 at 16:40
  • 2
    @Andomar, if you look at one of the large ERP/CRM applications you will find the customer table related to so many table. Inserting a customer in this case is pretty simple. Anyway, coding is not the concern. Data integrity should be if you want to develop a quality system.
    – NoChance
    Aug 20 '12 at 17:01
  • why do you have city and state in customer? Sep 16 '12 at 19:06

To normalize the design, you have to seperate the CITY. Besides saving some space, its good design. Also alleviates duplicate city name entries, like Foo, foo, Fooo etc and you can have details specific to city in that table like zip code.

  • 1.How does this alleviates duplicate city name entries? Right now I have a combobox populated from distinct query with an option to add new city, if I seperate it to another table they can still add city 'foo' even though 'Foo' already exists? 2.Do you mean there's something else beyond what u mentioned explicitly by saying "its good design"?
    – BornToCode
    Aug 19 '12 at 16:41
  • 1
    As long as no other column depends on City, there is no normal form that requires separating it out. Would you suggest adding a Housenumber table as well?
    – Andomar
    Aug 19 '12 at 18:27
  • @BornToCode my thought was by having a separate city table, you can easily control the list of cities and offer a drop down in the user interface. But if customers are going to enter a NEW city name, then this might not work; unless you have a standardization routine, that looks up all forms of city name and figures out that its the same city, like NYC newyorkcity, new your city mean the same thing. On second thought, Creating a separate address table would normalize the design. For instance, the address is of an apartment, where multiple customers have lived. It depends on various use cases
    – Shiva
    Aug 20 '12 at 14:47

He also mentioned that because I have duplicate columns - city: foo, city: bar, city: foo. (few customers in the same city) this is not considered normalized, is this true?

The Wikipedia article on 3NF says: a table is in 3NF if and only if, for each of its functional dependencies X → A, at least one of the following conditions holds:

  • X contains A (that is, X → A is trivial functional dependency)
  • Or X is a superkey
  • A-X, the set difference between A and X is a prime attribute (i.e., A-X is contained within a candidate key)

City cannot be said to functionally dependent on itself. So the repetition of identical values in different rows does not violate 3NF.

For an example that would violate 3NF, add CityPopulation to the table. Now there is a functional dependency for X = (city) and A = (CityPopulation). This dependency satisfies none of the three conditions, and this table design would violate normal form.

Having said that, I think normalization is a pointless academic exercise. Striving for 3NF compliance in a database is a guaranteed catastrophe.

  • I must say you make an impression that you're pretty good in these academic excersices.. Would you consider separating cities anyway in case that in the future there might be added city_population or for the other benefits mentioned (some space&faster populating combobox) even that it doesn't violate normalization?
    – BornToCode
    Aug 19 '12 at 19:36
  • 1
    @Andomar - could you expand on "striving for 3NF compliance in a database is a guaranteed catastrophe"? Isn't the point of 3NF to prevent data corruption issues that can occur in lower forms of normalization? Aug 19 '12 at 19:45
  • @ChrisSaxon: Much more eloquent people have explained that. Search for overnormalization
    – Andomar
    Aug 19 '12 at 19:49
  • 1
    @BornToCode: Anticipation of possible future changes is no reason to separate out a column. You Ain't Gonna Need It. Enterprise applications like SAP R/3, Siebel or Oracle don't separate out City. There's no reason you should.
    – Andomar
    Aug 19 '12 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.