0

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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Eli Braginskiy
  • 2,867
  • 5
  • 31
  • 46
  • Why should it violate the definition? Why can't you determine whether it does? Find a definition in a textbook. Try it. Show your work. Ask when stuck. The definition will talk about CKs. And it won't mention PKs. Your "avoid dependencies on no key attribute" is so unclear that it is useless. But even it were clear it would not be a correct definition because it doesn't mention CKs. How can you reason correctly if you are not clear & you don't get definitions right? – philipxy Jan 15 '18 at 05:24
  • @philipxy You dont have to be so negative about it, i obviously researched my self and couldn't understand the topic right? If I did I wouldn't be asking here, And my definition is taken from my text study. – Eli Braginskiy Jan 19 '18 at 11:11
  • I wrote a bunch of helpful questions & facts. I don't know how you get "negativity" from that. What happens when you answer/address them? (I don't know what definition you are talking about, because as I already pointed out you don't give or apply it, twice you ask us to.) An answer to your question is some chapter(s) from a textbook, so reference one & show your work following it & say where you are stuck. (Dozens of information modeling & database design textbooks are free online.) – philipxy Jan 19 '18 at 11:44

2 Answers2

2

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • So, it does not violate the third form? is that correct? – Eli Braginskiy Jan 14 '18 at 16:29
  • Adding a surrogate does not change what other CKs are present or what NFs a table is in. So it is irrelevant to identifying or eliminating update anomalies addressed by normalization. – philipxy Jan 15 '18 at 05:21
1

You should not break the tables into two because in 'User' schema there are three columns(username, SSN, UID) that uniquely identifiable and if you are taking the 'username' as primary key then other two columns(SSN, UID) are alternative primary key which is called 'candidate key' in User schema . & this should not violate 3NF .

Usman
  • 1,983
  • 15
  • 28