1

I have a table which has three columns: account, idNumber, and position. The account and idNumber are unique.

Here is my thought to analyze and I don't know whether it is right or not.

account is primary key and account ->idNumber, account->position, 
idNumber->account, idNumber->position

But I think that the idNumber->position dependency violates the Third Normal Form (3NF) rules (I am not sure.)

So, I decomposed it into two tables: one has account and idNumber, the other has account and position. But this seems to waste storage space with one more account column.

So, I am confused - is the method I used correct?

1
  • Wasted space is a minor issue when considering normalization or the alternatives. Two bigger issues are whether you are going to allow the database to get into a self contradictory state or not, and how much of a performance hit are you going to take doing joins. Commented May 18, 2013 at 10:37

1 Answer 1

1

If it's a table of 3 columns, you're probably overthinking this. Strict 3NF is all well and good, but when you're talking about possibly having two tables just to follow it precisely, it's time to step away from the table, nobody's getting hurt here...

The intent of 3NF is that each table should contain unique keys, and all of the columns should describe those keys. Placing data about a person and that person's workplace in one table doesn't make sense - you're really describing two things, so you should separate them.

In this case, it sounds like (and it's not totally clear, but I'm guessing) account and idNumber are both effectively keys. If they're both unique in the table, with a 1:1 relationship, then they're both keys, and even if one of them is the primary key, they both serve that purpose. So why not keep them in the same table? You're not describing two different things here, are you? It sounds like you're describing the same thing in two different ways.

Now, the fact that you have two unique IDs for the same records begs the question as to why. Are these IDs from two different groups of [other] tables? If that's the case, then you may need to have an account-idnumber only relationship stored in a table in order to more easily convert between them. If that's not the case, do you really need both of these columns at all?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.