2

Suppose I have this:

ID A_Type B_Type C_Type
1 Y N N
2 N Y Y

And I "flip" those columns into this:

ID Type Value
1 A Y
1 B N
1 C N
2 A N
2 B Y
2 C Y

I know this is fine to do. I know it means I don't have to change the target table in the second design if I have a new type, where I would have to in the first design. That is, I get the pros and cons and such.

But I'm interested in the theory. What is the normal form of the first table, and what is the normal form of the second table?

Most things about normalization give you a normal form, then show an example. I'm trying to go the other direction. We do this pattern all the time, so given this example, what is the normal form? Both before and after.

Thanks!

2
  • As a transform, I suppose it falls under "unpivoting". But in terms of design and "normal forms", the so-called EAV approach is widely thought to be an example of denormalisation. However it's not always possible to give concrete answers based on an example of the structure alone, without talking about the meaning and nature of the data itself. For example, are all the values actually bools, or was that just adopted for the purposes of the abstract illustration?
    – Steve
    Sep 10, 2020 at 19:26
  • 2
    A NF is a certain condition that a relation value or variable satisfies or doesn't. A transformation doesn't have a NF & isn't a NF. Its output could be in some NF. But this transformation has nothing to do with normalization. What do you think the word means? What exactly are you trying to ask, without using that word? What is stopping you from finding the NFs of these examples? Otherwise those questions in this post are faqs, unresearched & asking for a textbook to be (re)written. PS This is an unpivot.
    – philipxy
    Sep 12, 2020 at 23:27

3 Answers 3

4

The normalization does not only depend on the structure of the table, but also on their data content.

Both tables seem to be at least in first normal form, because the data is atomic.

The second table seem to be at least in third normal form:

  • Its primary key seems to be ID, Type
  • the remaining value seems to depend solely on the primary key, and not on a subset of the primary key. (2nd normal form).
  • moreover value does not depend on any non-key attribute (3rd normal form)
  • in fact, since ID and Type are independent (i.e. for each ID you can have all the Types and vis-versa), it could even be in Boyce-Codd normal form.

For the first table, we cannot be so affirmative, because we have to few data:

  • If could be in second normal form, since the set of values seem to depend on the primary key ID enter code here and nothing else.
  • If could be in 3rd normal form, but nothing garantees us that there is not some transitive dependency. We could very well have the case that only the Type_A is dependent on the primary key, but that Type_B and Type_C are dependent on Type_A and not directly from the primary key. In fact you have this problem in your example data, since Type_B and Type_C are both the negation of Type_A. To get a 3rd normal form you'd have to split up in two tables: (ID, Type_A) and (Type_A, Type_B, Type_C)
  • If more data would however demonstrate that this is not the case and that it's in the third normal form, you could then deduce Boyce-Codd normal form because the the primary key has only one component, so that there couldn't be a hidden dependency there.
3
  • Excellent analysis. In our actual business cases where this comes up, they Type_A, Type_B, and Type_C fields are independent, and that would be clear from enough data, so it's a lack of data issue here. Sep 11, 2020 at 13:25
  • Interestingly though, if both are in 3NF, then really this is more of a "generalization/specialization" notion than a "normalization" notion. Thoughts? Sep 11, 2020 at 13:45
  • @JamesMadison Indeed. And more precisely generalisation of code: solution 1 fixes the structure. Adding columns requires to change database on one side and code on the other. Solution 2 allows to add new types without need to change the db, and you may design code to exploit this flexibility. But the downside is that code and queries become more complex.
    – Christophe
    Sep 11, 2020 at 14:02
0

This does not apply to the concept of normalization.
It is however a difference between structure being fixed in the table, versus being encoded in the data.

Compare 1.

ID    YesNo
----  ------
1     Y
2     N

with 2.

Row   Column  Value
----  ------  -------
1     YesNo   Y
2     YesNo   N

1 would need to change the table to add more columns, however 2 does not need.

This technique is used by Content Management Systems (CMS) such as wordpress, drupal, joomla, etc. to store user given data, in a fixed table structure.

0

The first normal form is about the structure of the tables, but the other normal forms are about functional dependencies between attributes.

Both tables seem to be in first normal form. But without further explanation of what A, B and C means, we can't guess what the functional dependencies between them are.

Lets say Type_A means "is a dog" and Type_B means "is not a dog" then you would have a functional dependency, since the value of A determines the value if B. If Type_B instead means "is grumpy", then the attributes are not dependent.

If there is a functional dependency we still cant say which normal form is violated since you don't indicate which attributes are primary keys.

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