Re: 3NF question

You didn't make a mistake in using the ID column, but because you tried to present two different entities as a single one (USERS). There is actually an entity that represents users and an entity representing access via login/password. The example you gave is closer to a
"relationship" rather than an "entity". In order to solve this
problem you need two entities: USER and LOGON, and a relationship: USERLOG. Loosely speaking, this solution can be: USER (userid, ...)
LOGON (logid, password,...)
USERLOG (userlogid, userid, logid,...)
Of course, this application can be very complex in a real life situation.
Although the column "ID" is not a good solution in your example (as above mentioned), generally speaking, it is a good idea to use
"ID" as a key. The fact that the name of this column is "ID" is
OK, because it is a reminder of an identifier, which, by the way, is the essence of a key in a relational system. We prefer to say
"identifier for a tuple" in place of "name for tuple" because
this is more appropriate regarding the key's purpose. In a relational database you can choose anything to be your key (you can use auto-number logic, your own software, etc.) under one condition: that it satisfies the definition of a key, which is simple. (you can create a procedure which checks whether a certain name is ok for a key in your application) So regarding your relational database, it isn't necessary that the key be anything special - a key should be an identifier.
"The reason candidate keys are so important is that they provide the
basic tuple-level addressing mechanism in a relational system" (C.J. Date).
We often use some existing attributes for a key, that is, we don't add a new column "ID". Although, this is a simpler approach, we should be careful. Let UserName be a key, then for example the name John Smith (from this column) has two meanings. One is the real man John Smith and the other meaning is that it is one tuple in the database. This can cause problems within the functionality of the database.
Lastly, I get the impression that your question about the meaning of 3NF was caused by the question about two (or more) keys in a single table. I think that this is a long story in both relational and conceptual models.

Vladimir Odrljin Received on Sun Jan 09 2005 - 19:53:46 CET