When does repeating data get removed: before/after normalization?

sql normalization database-normalization

140 просмотра

1 ответ

2737 Репутация автора

Given this logical design:

R(a,b, c, d)
a is the only key. I can't underline it using this editor. 

It's in BCNF because there are no composite keys, no transitive dependencies, and no partial key dependencies.

However, we still have repeating data across rows in the attributes b, c, and d.

Do we introduce surrogate keys and rewrite it this way:

R(a, bID, cID, dID)
R1(bID, b)
R2(cID, c)
R3(dID, d)

if so, does that happen before or after normalization?

Автор: nicomp Источник Размещён: 04.04.2017 09:11

Ответы (1)

3 плюса

11640 Репутация автора

The point of normalization is not to remove repetition. It is to remove inappropriate dependencies. If every non-key attribute is fully functionally dependent on the primary key (and nothing else) then it doesn't matter for purposes of normalization that from one row to another in a table that some column data may be the same. That sameness is incidental.

Here is the thing you have to think about when looking at repetition and deciding whether it is incidental or meaningful. Consider the case of an update to a non-key column.

In one scenario, let's say that the non-key column is a person's name. What happens in your system when someone changes their name? If the old value is "Doug" and the new value is "Bob" do you want every instance of "Doug" to be replaced by "Bob"? Maybe you do, but I'm guessing you probably don't. If you were to create surrogate keys and normalize out the non-key value to another table then you would be incorrectly changing values that you don't mean to change.

In another scenario, let's say the non-key column is a municipality name. What happens in your system when you change a municipality name? Let's say the old value is "New Berlin" and the new value is "Kitchener". Would you want every instance of "New Berlin" to become "Kitchener"? Maybe so. (perhaps not, it depends on your business rules) = If you do want to change every instance then what you've discovered is that the municipality name may not be fully functionally dependent on your primary key. In that case you should normalize it out to a new table.

You have asked when this should happen (before or after normalization). The answer is that it happens as part of the normalization. The act of moving data off into a separate relation in order to avoid a partial or transitive functional dependency is itself the act of normalizing your database schema. Is this part of 2NF or 3NF? It depends. If your non-key attribute is partially functionally dependent on the key then it's during 2NF. If it's transitively dependent (i.e. dependent on another non-key attribute or attributes) then it's during 3NF.

You should perform normalization as part of the logical modeling process as much as possible. When you get to the physical model you are more likely to introduce denormalization for one or another of some practical reasons. Denormalization (in transaction processing systems) is something you should generally do only when you find that you have to. 3NF or higher is a good stake in the ground for OLTP systems. Therefore, you will have built your logical and your physical schemas before you start denormalizing in most cases.

Автор: Joel Brown Размещён: 04.04.2017 10:27
Вопросы из категории :