47

I've started working at a new organization and one of the patterns I've been seeing in the database is duplicating fields to make writing queries easier for the business analysts. We're using Django and its ORM.

In one case, we keep a MedicalRecordNumber object with a unique string identifying a patient in a certain context. We have Registration objects which track patients and have associated MedicalRecordNumbers, but rather than using a foreign key relationship, they duplicate the string so they can avoid writing a join (not for performance reasons). This pattern is common throughout the database.

For me the importance of a data model being clean is just so I can think about it well. Needless complexity is a waste of my limited cognitive processing time. It's a systematic problem. Not being comfortable writing joins is a rectifiable skills issue. I don't necessarily want to advocate going back and changing the schema, but I'd love to be able to convincingly articulate the problems with this type of duplication.

Improve this question
20
  • 2
    What does it mean to "not be comfortable writing joins"? How do they explain that?
    – scriptin
    Apr 11, 2015 at 16:48
  • 9
    Do these folks work for you? Are you their supervisor? Most of your justifications can be found here: en.wikipedia.org/wiki/Database_normalization. Yes, they need to get better at using joins.
    – Robert Harvey
    Apr 11, 2015 at 17:00
  • 1
    Have you looked up the literature on why normalization is desirable?
    – Nathan Tuggy
    Apr 11, 2015 at 17:02
  • 17
    Wouldn't adding views that do the join internally make writing queries just as easy? You could suggest them as an alternative.
    – CodesInChaos
    Apr 12, 2015 at 10:14
  • 1
    Did you communicate this (politely) with your peers and seniors? What are their justifications, what considerations are they making? There are many possible reasons why this might be a good idea (even though you say "performance is not the reason", what evidence you have to support that?). Before accusing them of being too lazy and/or rigid, have you considered (and asked) the reasons they have for having the design the way it is? Maybe there's far more reads than writes (analytics heavy DB)? Change tracking? Historical data? Ask everyone - someone might know the real reason.
    – Luaan
    Apr 14, 2015 at 7:39

7 Answers 7

Reset to default
129

Your operational database should be highly normalized, to reduce anomalies.

Your analytic database (warehouse) should be highly denormalized, to ease analysis.

If you don't have a separate analytic database, you should make some highly denormalized [materialized] views.

If you tell your senior business analysts / managers to do lots of joins for a simple analysis, well, you might get fired.

Agile Data Warehouse Design is a good book

See my quick n' dirty data warehouse tips here

Improve this answer
12
  • 9
    This is the right way to go.
    – Etheryte
    Apr 11, 2015 at 22:23
  • 6
    +1 This is exactly what Views are intended for: allowing a denormalized view on a normalized database.
    – Nzall
    Apr 12, 2015 at 15:58
  • 4
    Absolutely correct, but I think "reduce anomalies" should be emphasized more, since that's the primary answer to the question. The most common (only?) anomaly you'll see with data duplication/denormalization is that the columns will somehow get populated with contradictory data at the same time, leaving you with no way of knowing what the real data is supposed to be and no way of determining what went wrong. The latter can be mitigated with massive tracking of changes, but this won't be cheap or quick to go through and find the problem. More cost effective to avoid the problem entirely.
    – jpmc26
    Apr 12, 2015 at 22:23
  • 2
    Another angle to consider is that, even assuming the developers are capable of keeping the data correct (doubtful), it becomes a huge drain on their resources to ensure that every duplicate field gets updated when required to maintain consistency.
    – Nate C-K
    Apr 13, 2015 at 4:03
  • 1
    @Panzercrisis The only way a transaction is "implicit" is if you have an automatic commit running at the end of your query. This should not usually be the case for a production database. In an application, transactions should be initiated automatically and a commit should be issued separately from the query. This is a small upfront investment in the application, but it simplifies code changes that involve adding database calls and reduces how much a developer has to think about (improves dev speed, reduces dev errors). That sort of design also fits in well with things like connection pooling.
    – jpmc26
    Apr 13, 2015 at 17:28
57

I understand, why somebody wants to avoid writing a join for each select.

But you can create once a view with the join and use it instead of your unnormalized table.

So you combine the advantage of normalization with the convenience of an easy select.

Improve this answer
1
  • 12
    Views are your friends. Use them liberally. And for performance, you could even use Materialized views if your RDBMS supports them.
    – VH-NZZ
    Apr 12, 2015 at 9:04
13

The answers which have already been upvoted pretty much cover the "how to avoid duplication" (using views) but not the why. They basically show that duplication of columns is the wrong solution to the problem of making it easier to write queries. But the question "why not duplicate any random column just for the heck of it?" still stands.

The answer is "Because of Murphy's Law". Murphy's law states that:

If something can go wrong, it will.

In this case, the contents of each row field of a duplicated column are supposed to be identical to the contents of each corresponding row field of the original column. What can go wrong, is that the contents of some row fields may differ from the originals, wreaking havoc. You might think that you have taken all conceivable precautions to ensure that they will not differ, but Murphy's law states that since they can differ, they will differ. And havoc will ensue.

As an example of how this may happen, simply consider the fact that the duplicated columns don't get filled by magic; someone must actually write code which stores values in them whenever rows are created in the original table, and someone must write code which keeps updating them whenever the originals get modified. Setting aside the fact that this is adding undue burden to code which enters data into the database, (and which is, by definition, far more crucial than any code which simply queries the database,) someone, somewhere, under certain circumstances, might forget to carry out this duplication. Then, the values will differ. Or they may remember to carry out the duplication, but not within a transaction, so it may, under certain rare fault conditions, be omitted. But I did not really need to waste my time writing these examples, and you did not really need to waste your time reading them: the beauty of Murphy's Law is that it saves us from having to come up with examples of how something may go wrong on a case by case basis: if it can go wrong, it will.

Improve this answer
12

Thinking of it in terms of tradeoffs rather than good/bad will be more productive. They are trading off advantages of normalization (esp. consistency) for advantages in query usability.

At one extreme, the database would become useless if the data got severely inconsistent. At the other extreme, the database would be useless if it's too difficult for the people who need to query it every day to get results they can count on.

What can you do to reduce the risks and the costs?

  • Build a consistency checker tool and run it regularly.
  • Route write access through software that updates the replicated data consistently.
  • Add views or build query tools that do the joins automatically so the business people can think in terms of the information rather than the DB internals.
Improve this answer
6

I think the strongest argument for data normalization for business analysts is that it promotes data integrity. If your key data is stored in only one place (one column, in one table), it's much less likely that the data will get corrupted by incorrect updates. I think they would probably care about the importance of data integrity, so this this might be a good way to convince them to update their ways of interacting with the database.

A slightly more difficult method of querying is likely going to be preferable to potential data corruption.

Improve this answer
4
  • 6
    His people will argue that they're good enough to make sure that all of the data is being updated properly (a premise I dispute, if they're uncomfortable with joins). Perhaps a better argument is that you lose most of the benefits of ACID that RDBMS's provide, if you eschew normalization.
    – Robert Harvey
    Apr 11, 2015 at 17:23
  • 4
    Probably, but it's all a question of risk. Are they willing to accept the risk of corrupting the database because it makes querying easier?
    – Oleksi
    Apr 11, 2015 at 17:25
  • 1
    Playing the devil's advocate here, an obvious counterargument would be that, if someone's going to screw up an update and corrupt data anyway, that's a problem with or without normalization -- and, at least, having some redundancy in the database makes it more likely that someone will notice the corruption, and may even be able to fix it later. (Of course, ad hoc denormalization is hardly the most reliable error detection scheme, but the principle of error checking via redundancy is sound: that's how double-entry bookkeeping works.)
    – Ilmari Karonen
    Apr 12, 2015 at 14:32
  • Or, to put it in other terms, there's more to data integrity than just relational integrity. With a fully normalized database, you can still maintain perfect relational integrity even if someone messes up an update, but that doesn't make the incorrectly updated data any less garbage.
    – Ilmari Karonen
    Apr 12, 2015 at 14:35
0

To add to what the other guys have suggested above. This is a data governance issue. You need to work with relevant stakeholders: data architects and data stewards to develop data principles, policies and naming conventions.

Be patient and work methodically. Change won't happen over night.

Improve this answer
0

Quit.

Honestly, you can spend months arguing about normalization, consistency, and fighting crazy bugs caused by sheer laziness, and then quit.

Or you can just save time, and frustration and quit now.

Good programmers are very lazy people. They understand customer and management needs. But most importantly they understand that solving problems well, using well designed, and well implemented solutions saves them personally HUGE amounts of work, effort, and most importantly agony and stress.

So you would be much better working at a place that understands and values good engineering.

Good Luck.

Afterthought : Maybe what they need are BI / OLAP tools... http://en.wikipedia.org/wiki/Online_analytical_processing

Improve this answer

Your Answer

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

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