In accordance to the third normal form, we need to avoid dependencies on no key attribute.
So if i have a database of users
User(username varchar, full_name varchar, country varchar, SSN varchar, UID varchar)
And for every user I have his username, a full name, country, and a social security number (which is unique), and another number which is unique for every user. I want to use the username as the PRIMARY KEY, however if you know a person social security number you can also get all the information about that user since its also 'UNIQUE'.
Doesn't it violate the third normal form?
I could split it to two or more tables, for example remove SSN from User
and put it in a different table
SSN(ssn varchar, username varchar)
However now I have the same problem in this table, since two keys can be used as the 'PRIMARY KEY'.
Is it okay? or does it violate the third form, and if it does, is there any clever way to solve this?
It is common in databases to have multiple columns that might all be unique. You have clearly articulated a situation where this is the case. These are called candidate primary keys.
In such a case, each of these columns should be explicitly declared as unique and not null. However, only one column can be the primary key.
In general, I am in favor of synthetic primary keys -- a numeric values that is auto-incremented. The exact syntax varies depending on the database, but it most databases support such keys.