1

I created a relation called Customer and defined it as:

enter image description here

I just wanted to know if it is in 3NF?

My concern is regarding address, since multiple customers may have the same address.

I thought, since there are duplicate values for address, it is not in 2NF. How can I solve this?

Should I separate name as firstName, lastName, and address as (Country, City,...)?

  • 2
    Separate values from address column into two tables (e.g. cities and countries), in table customer create two new columns for city_id and country_id and link these columns via FKs to new tables. – user1315357 Mar 8 '13 at 9:31
  • @Westie: the whole name in one field is actually a brilliant idea. – ypercubeᵀᴹ Mar 8 '13 at 17:19
  • 1
    I would add that it can be quite valid to string an entire address in a single string as long as the elements of that string are not considered distinct for query/reporting purposes. In this case each field includes only one value, as is the case here. It isn't necessarily a normalization violation to do what you have done provided that the country of the customer is not important as anything other than a part of the address. – Chris Travers Mar 10 '13 at 5:37
  • 3NF depends on FDs. But you don't mention them. – philipxy Nov 15 '18 at 9:12
6

In the end whether the table is 1NF and 3NF depends on what the domain value of a city is. This answers whether or not you have atomicity.

In essence what normalization means in the abstract regarding address information is quite controversial and it is not an easy area to answer and so I personally believe that this must be addressed on a case by case basis, based on the semantic use of the data.

Otherwise consider the following addresses:

418 N Bradley St
Chelan, WA 98816

How many atomic fields would this require with maximum decomposition? So we consider this to be:

CREATE TABLE address (
    id int not null unique,
    street_segment char(1),
    street_name varchar not null,
    city_name varchar,
    state_province varchar,
    mail_code
);

That seems straight forward enough until you get an address that requires a cross-street as is common in South America, then we start to add fields. But now you have to track an address in Managua like:

Bo Altagracia km 3 1/2 carretera sur | Montoya 3c al Oe

Note that this is an ordinal address based on landmarks, distance and direction, and it does not fit into this data model at all and a normalized representation would require at least one additional table and some additional fields, and we can no longer enforce the same not null constraints.

But then this doesn't necessarily mean there is anything breaking 1NF if we just store the address as a text string up through and including the country.

If you have no reason to track countries, then one may see "London, England" as a valid representation of a city designation and then yes, we could see this as 1NF. In essence such a table has a city domain (where "London, England" is distinct from "London, UK" and "Paris, France" is distinct from "Paris, North Carolina"). This gives you a number of possible issues, but it does not pose classic normalization problems unless you need to track countries (here the country is just an incidental part of the city's domain value, not a domain in itself).

So in this case it may or may not be 1NF depending on how you define the domain of the address to be and what you have to track it relative to. If it is 1NF then it is also 3NF.

However if you break city and country into separate fields, then you have a normalization problem because city is dependent on country, and so to be truly 1NF you'd probably need to break out your regional hierarchies quite a bit more (a cities table, a state/province table, a country table).

A typical approach to this is exactly what you have done, which is to treat the address as a text string atomic domain, and not break things out any further.

Edit: Another example of 1NF problems using arrays

Suppose on PostgreSQL I am storing IP addresses and will be querying on octets. I might represent an IP address as a smallint[] array like: array[192,168,1,101,24] instead of a cidr representation like '192.168.1.101/24' and this does not break 1NF. Each array of smallints is distinct in its domain and each represents a single value of its domain. This does not break 1NF because in the domain of an IP address, each array represents a single value in its domain (and this is ensured by the fact that ordinality is important). This is a good example of why it is wrong to assume, for example, that the inclusion of complex data structures or arrays necessarily violates 1NF.

Finally if "this datatype can be decomposed and therefore it isn't atomic" breaks 1NF then so does every use of a datetime datatype....

TL;DR

1NF's atomicity requirement is violated only when the column stores two or more values within a given domain. That is not the case in the initial question so 1NF is not violated given the information shown. Given that 1NF is not violated and both name and address are functions of customerid, and not functions of eachother, the requirements of 3NF are met.

Your Answer

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