If a table with a surrogate key has a column known to have unique non null values (eg SSN), does it violate 3NF?

Question

As I understand it, third normal form (3NF) basically means there should be exactly one key.

If a table with say an auto-increment id column also has a column known to be unique and not null, eg social security number, this other column could be used as the key.

Ignoring practical/business issues (eg ecurity/privacy risk when passing around SSN as a key/FK), from a strictly schema design aspect, would such a table not be in 3NF because there are effectively 2 keys?

Would the answer vary on whether there was a unique key on the other column? If so, why?

Answer 1

As I understand it, third normal form (3NF) basically means there should be exactly one key.

No. 2NF, 3NF, and Boyce Codd Normal Form (BCNF) deal with functional dependencies. A table in 2NF means there are no partial key dependencies where a non-key column is dependent on some proper subset of a multi-column key. Tables such as the one in our example are already in 2NF as each candidate key is a single column. A table in 3NF means every non-key column is also not functionally dependent on some other non-key column, and thus creating a transitive dependency. It does not matter if there are one or a hundred candidate keys. Actually it is BCNF, not 3NF, which is the "final" normal form with regard to functional dependancies. This is because a table can be in 3NF yet not be in BCNF as there could be multiple candidate keys which overlap. Thus, when we use the term 3NF to mean "fully normalized" with respect to functional dependencies, what we really mean is BCNF.

If a table with say an auto-increment id column also has a column known to be unique and not null, eg social security number, this other column could be used as the key.

Not only could it be, it must be if we want to ensure the data stored in the database remains consistent with the rules we have identified in the real world!

Ignoring practical/business issues (eg ecurity/privacy risk when passing around SSN as a key/FK), from a strictly schema design aspect, would such a table not be in 3NF because there are effectively 2 keys?

As explained above, whether or not the table is in 3NF (or more importantly BCNF) is orthogonal to how many candidate keys it contains.

Would the answer vary on whether there was a unique key on the other column? If so, why?

No, simply because determining if the table is or is not in 3NF has nothing to do with how many candidate keys it has. It instead has everything to do with ensuring all the non-key columns are fully functionally dependent on those candidate keys.

But this does bring up an interesting point. Note that a unique key when defined as a constraint in a DBMS is not the same as a unique identifier defined as a business rule in a conceptual business model. Perhaps in our world we always know the person's SSN and thus it serves as a candidate key for a person, and perhaps we also introduce a surrogate key in the logical schema we call Person Id. Our business model includes the rule stating that SSN is a unique identifier for a person in our world. This implies a functional dependency of all the descriptive attributes on this identity attribute. This rule does not change just because we either forgot to or chose not to inform the DBMS. This is precisely why it is vital the constraint be declared - so that the DBMS can ensure the data stored is consistent with the rules of the business model! If we didn't create that unique constraint on SSN we can now inadvertently create more than one row for the same person with the same SSN; each row having a different Person Id!

An excellent primer on these topics is Fabian Pascal's Practical Database Foundation Series and Chris Date's Database Design and Relational Theory, from which this answer is derived. While each paper of Fabian's is a must read, paper #1 (which clearly defines the difference between the conceptual, logical, and physical levels) and paper #4 (which clearly defines the various kinds of keys) specifically address this question. Likewise, Chris' entire book is a must read while Part II is the section devoted to normalization with respect to functional dependency.

Answer 2

A relation R is in third normal form if every non-prime attribute of R is non-transitively dependent on each candidate key of R

E.F.Codd, 1971, Further Normalization of the Data Base Relational Model

It is implicit in the definition of a relation that a relation must have at least one key. Nothing about 3NF or any other Normal Form requires that a relation should have only one key.

Unfortunately books on database design and normalization have plentiful examples of relations with only a single key and rather fewer examples with more than one key. This strikes me as odd given that multiple keys appear to be very common practice these days. The dearth of practical examples in non-academic literature seems to be one cause of confusion about the role of keys in database design. Another cause of confusion is the popular mnemonic "nothing but the key". That phrase is usually attributed to Bill Kent but it isn't an accurate definition of 3NF.

Answer 3

Since the question is based on an interpretation of a rule, we should first look at that linked information, which is (emphasis mine):

2. all the attributes in a table are determined only by the candidate keys of that table and not by any non-prime attributes.

I think the confusion is a result of misinterpreting the term "candidate keys". There can be multiple candidate keys in a table. This is why we have modifier terms to further specify among this group: Primary and Alternate. If tables could have one, and only one, key, then the term "Primary" Key would be misleading and should have instead been called something else (maybe "Parent" or "Origin" or "Identifying", etc). But "Primary" implies that there can be "secondary" keys, and those are called "Alternate" keys.

Alternate keys are indicated in physical models through a Unique Constraint or Unique Index. It should also be noted that both types of Candidate Keys (Primary and Alternate), can be referenced by Foreign Keys (even though one generally wouldn't / shouldn't do such a thing without a very good reason!).

Would the answer vary on whether there was a unique key on the other column? If so, why?

No, because that is a matter of physical vs logical modeling. You can have a table that has an IDENTITY field but yet no Primary Key defined. The table and its data can easily be in 3NF, even if the physical model does not enforce that. This distinction is similar to whether or not Foreign Keys are defined. You can surely JOIN tables, and have no orphaned records, whether or not any PKs / FKs have been defined. And the data can be 100% correct without those constructs. But having the PKs and FKs defined is the difference between Referential Integrity (logical) and Declarative Referential Integrity (physical). Having the constraints in the physical model simply helps enforce the rules of the logical model.

---

With regards to SSN ("Social Security Number" for those who are not familiar with that acronym), and it being an Alternate Key, and having a Unique Index/Constraint on it:

I would recommend against considering an SSN being an Alternate Key and putting a Unique Constraint or Index on it, even if it is common to do so (SSN is often considered a "Natural" Key--one that exists out in the real world). There are two main reasons:

1. Accuracy: Most of the time, these values get entered into a system by someone filling out a form, whether on paper or online. People make mistakes while doing data entry all of the time, especially if the source was a paper form that is being entered by someone reading someone else's sloppy hand-writing (such as mine, which is barely legible).

   Even if the data comes from another system, can you be sure that the source system validated the info? Can you be sure that there wasn't a bug in their data export? What if there is a bug in your data import?

2. Uniqueness: Even if the main Social Security Administration has never issued a duplicate ID, that doesn't mean that duplication hasn't occurred. Outside of identity theft issues, I remember hearing from someone years ago who worked as a DBA for the states Department of Revenue (I believe) and who had to deal with Social Security benefits, how they were having "issues" dealing with what was an older practice of reassigning a deceased person's SSN to the surviving spouse (usually the widow) so that it was easier for the surviving spouse to continue collecting the benefit payments. I'm sure this practice was put to an end a while ago, but the "duplicate" data was still in the system.