Normalizing data past initial db design phase

Question

I wanted to poke at everyones brain to better understand when it makes sense to normalize a table that carries redundant data. I recently worked on a project at my startup and really question myself on whether it was a good idea. So the problem was something like this.

Our web app is a e-commerce web app. Our db contains a table that looks like this:

Store Product Table
-----------------------
- product_name
- price 
- store_product_id
- coupon_name

The data looks something like this:

pk, product_name, price , store_product_id, coupon_name
1, 'Orange Juice', 10.0, 2, 'ORANGE15PERCENT',
2, 'Orange Juice', 10.0, 2, 'ORANGE50PERCENT',
3, 'Orange Juice', 10.0, 2, 'ORANGE70PERCENT'
4, 'Milk', 9.0, 89, 'MILK20PERCENT',
5, 'Milk', 9.0, 89, 'MILK50PERCENT'
6, 'Ham', 5.0, 45, '', 
...
300, 'Jacket, 100.0, 90, 'JACK50PERCENT',

This is a relatively small table (~200 records). As you can tell there is a ton of redundancy. I saw redundancy and broke this out into two tables a Product table and Coupon table. This eliminated all the redundancy. I felt proud at first, but I wonder if it was really necessary. Should one always remove redundancy in a tables and break apart as two tables? Please note that this decision was made way past the initial db design phase. It's probably an easy decision to make when you are first designing things, but what about when you've built a codebase around a redundant table?

Note: I realize that there are pros to having redundant data if your table is a read heavy table, since this is a small table I don't think thats super relevant in this situation. Open to be proved wrong though.

Answer 1

Normalization exists to remove update anomalies. If a single fact changes in the real world, a single column of a single row should change in the model. In this way one source of inconsistency is eliminated. Pragmatically, normalization can be eased if

• there are no updates
• all the developers understand the situation and are competent to deal with it
• there are good QA processes in place
• inconsistencies are of little cost and easily fixed

.. amongst other considerations.

So, should you have normalized this particular table? In best DBA tradition I'll say "it depends."

Likely having a denormalized table causes problems right now. It may be the code is more complex, or silly errors slip in, or queries run slower or .. whatever. But there is a price and you know what it is. There is a risk that future events due to having a denormalized schema also will incur some cost. Maybe the wrong coupon is applied or too many rows get updated and revenue is lost. You know your business; you know what may go wrong and its severity.

Changing the schema and application will have a cost - direct effort and opportunity cost on features not implemented, releases delayed etc.

I'd say, if the discounted future cost of the current situation is greater than the implementation costs then do it. If not, add this item to the pile labeled "technical debt", monitor the situation, and get on with other stuff.

Answer 2

Rather than provide a direct answer, I want to challenge the premise of your question itself, and hopefully help you avoid some common pitfalls that many people (including myself) stumble on.

Is that data actually redundant?

While normalization yields many benefits (which Michael Green's answer already outlines), you have to make sure that the redundancies you are removing are actually redundancies.

I started my programming career with big ideas on the importance of normalization, and ended up creating a lot of database tables that were very normalized. The problem with that though, was while we gained the benefits of having normalized data, the business rules actually sometimes ended up requiring duplicate data in various places.

History

One legitimate reason to have duplicated data is to preserve a history of the data at the time it was entered.

For example, the product_name may change over time. 'Orange Juice' may have it's name changed to "High Pulp Orange Juice" (the only good kind of OJ).

How do you handle that business change? There are a few options, some better than others.

1. Force business people to create a new product, with the new name (easily done by just not providing a means to edit a product name). This has the obvious downside of making it harder to track sales of similar items over time.

2. Create a history table of all past names, and link your Store Product Table record to the correct row in that history table, which reflects the correct name at the time of purchase.

3. Do as the original creators of the table did and store a copy of the name at the time of purchase.

Any of the above will satisfy the common business requirement that when you query someone's order, you get back the information that was correct at the time the order was placed.

Easy updates

Another reason to have duplicate data is to allow for easy updates. At one company I worked for, the equivalent of your price column was what was updated if any change needed to be made in terms of what a customer was charged. All changes to what we were charging the customer for that specific purchase were made to that column, when such changes had to be applied.

We had a history table too, containing the original price, if that was ever needed for some reason. Eventually time was found to add a logging table to record all price modifications but that column still represented the actual amount owed, rather than the original purchase price or the price at time of checkout.

Business rules first, then normalization

My point is that without knowing the business rules for your business, it isn't really possible to say whether something was "right" to normalize, since we don't know if the changes you made were even normalization, or were actually removing legitimate features.

Assuming you did proper research with the business users and the programmers who originally designed the table (assuming they were around to ask), then Michael Green's answer is good (although, it also contains an important "It depends")

But just looking at the data itself there is no clear indication as to whether the data is actually duplicated, or if it is serving some other business purpose.