could i get an opinion, please, on whether or not these tables are "properly normalized"

say we have a users table with username, password, and fullname

case 1, we make username the PK

what normal form is it? at least 3rd, right?

okay, case 2, we add a surrogate key, userid, an autonumber-type column, and make this the PK

is this still normalized?

case 2a, what if we also add a unique constraint on the username

is this any more or less normalized than case 2?

AtDhVaAnNkCsE


oh, and if you're wondering, shouldn't i know the answers to this, yes, i believe i do, but i'd like a second opinion


"okay," says the doc, "you're ugly too"

In case 2, is there only one key, namely the surrogate? Or is username still a key?

So in case 1 there is one key. In case 2 there are two keys. In case 2a there are three keys.

If the above is true then they are all in 5NF.

pootle flump ur codings are working excelent.

yeah, username is still a key, because it would get a unique constraint, to prevent two users from having the same username

here's the guy i'm having problems with...

same scenario, a users table where username is the key, and now you add a surrogate autonumber key (ostensibly the reason for this is to allow a user to change his username without any ripple effect on the database, since all relationships would be built on the surrogate key)

he claims that this would no longer be normalized...i especially like the last bit... username no longer represents a fact about the user???

So essentially the question is "can a table with a surrogate key and non-key attributes be in first normal form?".

The guy is saying that the relation should be decomposed in to two relations related by a 1:1 relationship to be normalised? So:
user_1 {surrogate, username}
user_2 {username, password, fullname}
Candidate keys underlined, primary keys bolded.

In the case you've described he's talking nonsense (and poorly phrased nonsense). Is this someone on another forum? Is he someone you respect? I imagine he must be otherwise you wouldn't be double checking here.

pootle flump ur codings are working excelent.

Ouch. Just seen the thread. Now I see why.

pootle flump ur codings are working excelent.

you googled the thread?

so you see what i'm up against, a bombastic know-it-all who actually doesn't

the reason i asked here is because i am not 100% confident in my own ability to explain normalization, so i was hoping for some more ideas, one of which might allow me to make a succinct point that he would actually understand

I don't fully understand his statement - his definition of 3NF is not clear and suggests an error - I am not certain. The more I read it the more it sounds like he believes that a relation only ever has one key. It is certainly not a helpful summary of 3NF since it is subject to interpretation.

I have seen it very briefly presented once before that surrogate keys violate first normal form. Ultimately a surrogate key is just that - it is a unique column used in lieu (i.e. a surrogate, in place of) of the natural key(s) to represent the entity throughout the other entities of the database. That Date and even Pascal advocate their use in certain circumstances is enough for me.

pootle flump ur codings are working excelent.

Pootle flump inspired me to google the original thread as well.

This is wrong. If we put a unique constraint on username (and certainly, this is necessary to enforce the natural key), then username is a candidate key for the row, and by definition is a determinant for the surrogate key. He seems to misunderstand the whole reason a surrogate key was added in the first place: to create a "synonym" for the natural key.

id -> username, password, fullname
username -> id, password, fullname

We could choose either one. Obviously, we go with the first, since the whole reason for doing this is to allow changes to the username without causing cascading updates throughout the database.

So now we need a definition for 3NF. I like to use the one from Data Modeling Essentials:

"A table is in 3NF if the only determinants of nonkey columns are candidate keys."

Username remains a candidate key (obviously -- it's the natural key), but that doesn't mean the table isn't normalized.

thanks for the confirmation, futurity

i could point you to other examples of this gentleman's database delusions, but i'm not interested in humiliating him further (he does a splendid job of it all on his own)

well, okay, except for one example

in a different thread discussing surrogate versus natural keys, he claimed he had never, in several decades of working with databases, seen an example where a natural key didn't exist

so i walked him through the forums/threads/posts model, in which the natural key for the forum was the forum name, the natural key for the thread was the composite forum name plus thread name (since different forums could have threads with the same name), and so on

therefore, in the interest of practicality, it is necessary to assign an "id number" to each forum, thread, etc. -- much like vbulletin does it (see the url for this thread or this post)

his reply was that the id then "becomes" the natural key

preposterous, innit

The problem seems to resolve around nomenclature.
What the OP terms a "role" would be more appropriately described as an "Access Level".
It makes sense that users could have more than one role, and in such cases it should be split into a separate table. But Access Level is more likely unary, and should remain in the user table.

But (as you might guess), I think felgall is going way overboard about the dangers of having a numerical (surrogate) key.

"Adds several dozen other complications"?
Pish-tosh, and total FUD.

"The only time you should be adding extra numeric keys to tables is when they have no natural key, when the natural key is too long to be used, or the key value changes too frequently (say twice or more since you started reading this)."
A completely unsuportable and narrow-minded blanket-statement.

unfortunately this is what we get from him on a regular basis

dportas!! you are my hero!! (david has signed up and jumped in to the thread)

however, i'm afraid that your explanations of normalization will be ~way~ over his head -- i'm still struggling with them myself