Database Normalisation: 1-1 relationships and 3NF

Question

Is there a conflict between 1-1 relationships and 3NF?

For example, take the following table:

Customer Table:

CustomerID (Primary Key)
CustomerFirstname
CustomerLastname
CustomerLoginID
CustomerLoginPW

Now let's assume that Each CustomerID corresponds to only one CustomerLoginID and vice versa. So this is a 1-1 relationship.

It is stated that usually, 1-1 relationships are already normalised, and do not need to be normalised.

However, CustomerLoginPW can be determined by the CustomerID, but it can also be determined by the CustomerLoginID. This breaks 3NF, i.e a transitive dependency.

Therefore, should the table above be separated into two tables, with the CustomerLoginID and CustomerLoginPW put into a separate table, even though the relationship is a 1-1 ? Leaving it in one table as so many suggest with a 1-1 relationship seems to break 3NF?

nice question: i also wondered about 1-1 and 3NF: your example is a good illustration of that scenario. — WestCoastProjects, Oct 22 '18 at 14:45
btw: did you read the given answer by @Renzo? Maybe consider accepting it or comment what else you would expect. — WestCoastProjects, Oct 22 '18 at 14:46

score 3 · Answer 1 · answered Dec 20 '16 at 20:52

A relation is in 3NF if any non-prime attribute (that is an attribute which is not part of candidate key) depends on a candidate key (what is important for the definition of 3NF are the candidate keys, not the primary key). Since both CustomerID and CustomerLoginID are candidate keys, they determine each other and all the other attributes, so there are no dependencies that violates the 3NF.

So you do not need to split the table, since the relation is normalized.

Database Normalisation: 1-1 relationships and 3NF

1 Answers1