Suppose I have two tables on a database, and they have 10 columns one and 11 columns the other, where 10 of the columns are exactly the same on both.
What (if any) normalization rule am I violating?
+3
A:
Perhaps the rule of avoiding redundant data? (i.e. the same data in two tables)
RandomNoob
2010-03-04 18:37:56
+3
A:
if 10 of the 11 columns are the same, why can't this just be one table, where the 11th column is left blank (along with a possible 12th column to denote which type of data it is, i.e. which table it would have been in originally)?
GSto
2010-03-04 18:38:34
Because storing 5 values in 100M row table is not a Good Idea storage-wise, as an example
DVK
2010-03-04 18:40:51
If the two tables represent differant "things" it would be a bad idea. Each table should represent one thing or idea.
2010-03-04 21:27:34
@redcayuga it depends though. if they're really two different things, why are 10 of the 11 columns exactly the same? Can you think of an example where this would be the case?
GSto
2010-03-04 21:32:39
+6
A:
You are violating the Third Normal Form (3NF), because if mostly the same data is held in both tables, then every attribute of each table is not directly dependent on the key of its respective table.
Alison R.
2010-03-04 18:39:54
I would think that the only way you'd be in 3NF, if all ten columns are the key in the first table, would be if all eleven columns were the key in the second, so we'd be talking about different entities, not the same entity (table 1) vs. the same entity (table 2) plus one attribute.
Alison R.
2010-03-04 18:48:31
Thanks to all that responded, it really seems to be a case of 3rdNF violation.
Otávio Décio
2010-03-04 19:13:40
If all 10 columns were keys then you would not need two tables anyways because they are still duplicates in both tables.
Jonathan Czitkovics
2010-03-05 00:14:06
+3
A:
It depends what's in the tables.
If no records are related to each other (for instance, if one table is simply archived records originating in but removed from the first table) you're not violating any rules.
But if those are the same records in each table, you have a dependency problem — that eleventh column is dependent only on the key value from the record, not the additional columns. Assuming that all ten columns are not involved in the primary key, you've violated 3rd NF.
Larry Lustig
2010-03-04 18:41:42
+2
A:
If all 10 columns are part of your key, then Second Normal Form: Eliminating Redundant Data. Specifically, this falls under "Nonsurrogate Versus Surrogate Primary Keys" dilemma - to be honest, I don't recall either of those two choices to be "violating" 2NF, but the surrogate key is definitely closer to the spirit of 2NF
DVK
2010-03-04 18:44:08
+2
A:
Only primary keys may be redundant between tables. Having any amount of non primary key columns in multiple tables violates third normal form.
Jonathan Czitkovics
2010-03-04 18:45:47