Unfortunately Third Normal Form is often misquoted in this way and that can lead to a lot of confusion. The more precise description of 3NF is that a relation R satisfies 3NF if and only if, for every functional dependency A->B satisfied by R, ONE of the following three conditions is true. Either:
(1) B is a subset of A (i.e. A->B is “trivial”)
(2) A is a superkey
(3) B is a subset of a candidate key.
This could be written very loosely and informally as “every non-key attribute is dependent on every candidate key”. The critical and often overlooked words are every candidate key. Like all normal forms, 3NF is concerned with candidate keys and is entirely agnostic about primary keys. It’s very unfortunate that there are so many examples around that only show tables with one key and that go on to explain 3NF in terms of dependency on a single key without of making it clear that all the keys are equally important.
Also notice condition (3) in the above definition. Transitive (non-key) dependencies do not violate 3NF if the dependent attriubute(s) are part of a key. This is arguably a “mistake” in 3NF that is rectified by Boyce-Codd Normal Form (BCNF). BCNF is equivalent to the above definition with the last condition removed. Many informal descriptions of 3NF miss this last condition and therefore more accurately they describe BCNF instead of 3NF! This is a mistake, but a “good” one because BCNF is “stronger” than 3NF.
For an accurate understanding of 3NF and other normal forms I can recommend David Maier’s “Theory of Relational Databases”, Chris Date’s “Relational Database Dictionary” or “Foundations of Databases” (AKA “The Alice Book”) by Abiteboul et al.
Why does any of this matter?
If you think about it, to define 3NF based on primary keys alone wouldn’t even work. If only primary keys mattered then a bijective relationship (A->B, B->A) would be impossible to represent accurately while respecting 3NF because any relation containing both A and B would always violate 3NF. Bijection is a common data modelling scenario and certainly something that needs to be accommodated in any useful database design methodology. If a single primary key was all that mattered then 3NF would become of doubtful value when dealing with relations with more than one candidate key because either you would be forced to ignore some dependencies or you would have to accept that 3NF was automatically violated.
Another point to notice is that if normalization was only concerned with primary keys then 3NF really would be the ultimate normal form for those PK dependencies. That’s because non-key dependencies and join dependencies of the kind addressed by BCNF and 5NF would not be possible (because they assume there could be multiple keys). We know that isn’t the case and we know that dependencies on all keys are important. That is why 3NF is defined in terms of candidate keys and not primary keys.
What does any of this mean for the specific example in this thread? I’m always hesitant to comment on normalization problems unless the intended set of keys and dependencies is clearly stated. They have not been, so I’ll make some assumptions. If ID and UserName are both candidate keys and if the dependencies we want to enforce include ID->UserName, UserName->Password (…etc) and if Password is not a determinant then a table containing only (ID, UserName,Password) is in 3NF (in fact it’s in BCNF and 5NF as well
).
That’s a lot of assumptions but given those conditions then it seems that the table I just described would be “fully normalized”. Other interpretations might be possible - we only have column names to go on after all. It would help if anyone wishing to make points about normalization examples would state what keys and dependencies they are intending to enforce. Otherwise effective discussion on such things is virtually impossible.
Hope this helps.
David