2

I've gotten out into the real world recently, and for the first time have had to really think about database design, as I've been developing across the entire Java stack. With that I've realized that I don't fully understand the data-store side of things in terms of design, and I haven't seen much of the relationship between data-stores and reporting.

When I got started in my programming diploma we were taught the basics of database design, such as the importance of normalization and naming conventions, but I've never done much of this in the real world. So I wonder:

Is a correctly normalized database the only consideration when attempting to deliver a data store with solid data integrity, and that is easily reported on?

and

If so, does more normalization tend to lead to more reportable data?

  • 2
    Is proper tyre inflation all that's needed to guarantee a safe and enjoyable road trip? – Mike Nakis May 29 '15 at 15:46
  • The short answer is no. A data store with proper integrity involves data management at several levels. The DBMS level is just one of them. Normalization helps to eliminate self contradictory data. There are plenty of other problems that have nothing to do with normalization. – Walter Mitty May 30 '15 at 10:44
3

There are two aspects to the question, and each have slightly different concerns.

Is a correctly normalized database the only consideration when attempting to deliver a data store with solid data integrity, and that is easily reported on?

No. Data integrity also demands constraints.

  • Primary key constraints uniquely identify a record. This helps guard against duplicates, but does not necessarily prevent them.

  • Foreign key constraints help ensure that related data is kept in sync: a table for "customer phone numbers" should have a corresponding "customer," for example. Orphaned records and missing data harm data integrity.

  • Field/column constraints can help ensure data is valid. For example, perhaps a phone number is stored in a VARCHAR field but should not store letters or formatting, only numbers. A constraint can guarantee that if the data exists, it meets arbitrary criteria that make it valid for the given schema.

If so, does more normalization tend to lead to more reportable data?

Normalization tends to lead to less reportable data. The reason is that a typical RDBMS schema is designed around ORM, meaning "application objects." What looks like one object may require several tables:

  • A class that uses inheritance (i.e. has subclasses) requires one table per inheritance level in practice because child data members are not applicable to the superclass and should have their own table.

  • Related objects may have their own table. A customer with multiple phone numbers may be List<String> in the application, but the phone numbers may be in their own table in the schema forming a 0..* relationship.

Reports often are record based where a record is specific to the report. They often denormalize data to give a view of a specific table with related data mixed in. This is normally at odds with normalization and ORM.

What this means is that an object that you use quite easily in the application may explode into many table in the database schema, adding relationships with varying cardinality. This requires joins when writing a report query, some of which may be complex or require subqueries. I have seen report SQL queries with ten or more joins, correlated subqueries, aggregates, and other intermediate to advanced SQL features which add complexity and can harm query performance.


The typical way to deal with this as I have seen professionally is to have a separate set of denormalized reporting tables built for your reports. Use triggers or stored procedures to populate them. This is more work during persistence, but saves a lot of time and hair pulling when writing SQL for your reports.

You can also use application code: when you save an object and have it and its related objects in memory, construct a query to insert or update a record into your reporting tables. This may be easier and have faster run-time performance than relying on triggers.

  • Although I guess in an off-hand way normalization does lead to reportable data, just not data that's easily queried. In other words, the structure should be in place to accurately extract data, but the more normalized the database is, the harder it is to get the data out. – Canadian Coder May 29 '15 at 18:03
  • 1
    That is true, because normalized data has better integrity (duplicated fields are gone) meaning more accurate data. But those report queries can be simply awful to write to the point of it being too easy to write incorrect queries that end up returning bad results. – user22815 May 29 '15 at 18:07
3

"Normalization" has multiple meanings, and they lead to different answers for your question.

The normalization you learn in database classes is a well-defined theoretical concept. Whenever you read of data being in "1st normal form", "2nd normal form" etc., this is what's meant. This is fundamentally a good thing for several reasons, but it's not always the best thing to do for ease of reporting. Quite the contrary, a huge data store (data warehouse) might be deliberately de-normalized so that reporting will work better. (Example: a completely normalized system might contain every sale and its amount as a single record somewhere, but for yearly reporting it's much more efficient to have the monthly sums precomputed and stored as well.)

But data integrity can also mean data that are free from spurious duplicates, misspellings, missing field content, etc. Whenever you have customer records for both "Wiley Coyote" and "Wiley E. Coyote", that's probably an error that decreases the quality of your data, and probably the quality of your reports as well, although no normalization algorithm will catch it. Getting rid of such problems can also be called normalization (or deduplication, or many other things). It is much more difficult, but it almost always improves the value of your data - the question is whether it improves enough to pay for the effort.

  • Ok, so in general table normalization + consistent spellings amongst data is about the extent of what I'd want to focus on in terms of designing a data store. And for reporting I might need to look into de-normalizing considerations. – Canadian Coder May 29 '15 at 16:17
  • 2
    @CanadianCoder: "consistent spellings" is just one of many examples for possible requirements for "a data store with solid data integrity". Do not generalize from one example. – Doc Brown May 29 '15 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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