Where's the tradeoff between normalization (SQL View) and performance/fiability (SQL Table)

Question

I've got quite a long business process which eventually results into financial operations.

What matters in the end is quite exclusively these final operations, although I've got to keep a log of everything which led to it.

Since all the information contained into the final operations is available in other tables (used during the business process), it makes sense to use a view, but the view logic would be quite complicated (there are dozens of tables implicated), and I'm concerned that :

1. even with appropriate indexes, a table will probably be way faster (my table will eventually contain millions if items, and should be fully searchable on almost all its columns)

2. the view logic would be complicated, so I'm afraid it may complicate things in a few years if I want to evolve my business logic.

Because of those two reasons, I'm a bit tempted to write the data in a table at the end of my business process instead of relying on a view, but duplicating the data doesn't smells right (and it also looks a bit like premature optimization, but since it's such a central point in my design, I'd like to address the issue ASAP)

Have you ever faced such a choice? What did you decide?

Edit : creating a table would clearly lead to duplication in my situation, ie. the data written in the table exists somewhere else in the database and could be retrieved using only joins without any calculations.

Answer 1

I think you answered your question writing it down Brann.

This problem can be seen in this way: from one hand you have "real time data". You have fresh data and from them it's nice to create view to show "real time data" too.

But as time goes on, there are more data and logic changes. So it's good to have written down summaries of data you had some time ago. It's very pragmatic - you do not duplicate data, because you recalculate them and save into new table summary of them.

So when you think of it in this way, it's obvious that in this example new table will be better. As you write:

• Faster access
• Can have more complicated logic
• Have archive data unchanged when logic changes

So when you meet this (or part) of this criteria as you requirement than its not choice - you go into tables.

When I would use view is only when showing fresh data out of other fresh data. In very, very simple problems. And when it gets more complicated - you always switch to new table.

So do not be afraid to go into it. Having one summary table with faster access is very pretty solution and it's a sign of well formed database.

Take care of the design of this table - so when business logic changes - you do not need to change everything from one stone in this table. And then everything will be OK!

Answer 2

I'm for the new table in this situation. The view has many disadvantages - performance clearly, complexity, and logic lock in. However, IMHO the over-arching reason is that as the underlying data changes, so the value in your view will change also. In most instances this is a good thing, however, with financial operations isn't it better to have a fixed record of what occured.