Should I structure my DBs based on the data's true form, or how I plan to use that data?

Question

This is mostly a hypothetical question, but it's something I've been tossing around and I wondered what other people though. Here's a contrived example to illustrate: Let's say I'm building an app that lets users pick a few keywords and then shows them photos that are tagged with those keywords.

On one hand, I could create user, keyword and photo tables which store the data entities as they exist. This would let allow me to easily change the function of the app without needing to change the underlying data structures, but it also requires several queries to fetch the data I need for the app.

Or, I could just have a single user-photos table, and somehow stuff all the data in there. This would make it easier to pull the data I need for the app, but it would be harder to perform any other type of querying on that data. After all, a user-photo isn't really a thing -- it's just a convenient data structure for this one purpose.

This might be a bad example, but the main thrust of my question is: should your database structure be based on the actual underlying entities they're storing, or can you use shortcuts to make it easier to use the data for your purposes?

Answer 1

You are asking if you should normalize (split into three tables) or de-normalize. You've already figured out some of the pros and cons of both. In most cases, it's preferable to go with a higher degree of normalization. This allows your data to more closely represent the entities that you're dealing with, and in general this will make life easier.

In your case, I recommend the first option (though you might find it will need some tweaking - for example: the relationship between photos and keywords is many-to-many, so you might want a photos-keywords to store these relationships). You can still get the notion of user-photo with a query or view on the three underlying tables.

Situations where denormalization might be better usually happen in reporting systems.

For more reading:

Normalization: http://en.wikipedia.org/wiki/Database_normalization

Denormalization: http://en.wikipedia.org/wiki/Denormalization

Answer 2

I would go with a normalized database design always. One advantage of normalized database design is that normalization allows you to extend the database without re-work in most cases (I am yet to prove this idea...).

In your case:

The proposed design

user, keyword and photo

is not valid. This is because the relationship between a user and a keyword is a many-to-many relation.

also

user - photo

is not valid, where would you store the keywords and how would you relate them to users if you do this?

This would make it easier to pull the data I need for the app

Don't worry about reading the data. It is just a matter of some SQL that would take you few hours or less to build and tune.

Answer 3

What you're talking about is sometimes referred to as the difference between OLTP and OLAP databases. The only letter that differs is the T/A:

• T = Transactional
• A = Analytical

OLTP databases are optimized for data that's being changed all the time. That typically involves making sure it's normalized.

OLAP databases are optimized for reporting. They're typically de-normalized, something like a "star" table configuration.

Normally the OLTP databases are the production databases that are running all day long collecting data, and then there's some kind of process that takes the data from the OLTP database and reformats it into the OLAP database at regular intervals (nightly, for instance). That means some person sitting at their desk running historical reports can't see anything after last night, but they get better performance.

That means if you started building your application by including an OLAP database for your reporting, it's probably a case of premature optimization. You need the OLTP database to run your application, and the OLAP is an optimization to improve performance. Therefore, the correct approach is to start by designing a normalized database (in your case separate tables for each "entity").