Comment by salvadors | Hacker News Reader

Comment by salvadors | Hacker News Reader

Parent Full thread

Is your point here that the data being stored is insufficient (e.g. you'd want an end date, not just an active flag; this doesn't cope at all with prices changing over time, bulk discounts, or different customers paying different rates; there's no concept of invoices, or whether payment is due based on calendar months or based on opening date; etc) or that you're ignoring all that sort of stuff just to keep the example simple (so assume everyone pays a fixed rate per unit, due weekly; someone can't close their account until they're paid up; etc) but that you'd still want a more complex schema so as to be able to more easily generate a "Who owes us money?" report?

If it's the former, then sure: you need to be able to model all these things properly. If it's the latter, then I'm not so sure. The SQL to create that sort of report is going to be non-trivial, but it shouldn't be overly complex for someone who knows what they're doing, and if you have the correct indexes it shouldn't take very long to run either.

If you want to start doing all sorts of fancy data warehouse slicing and dicing, you're usually better extracting daily (or more/less frequent depending on needs) dumps of your transactional database into a different structure more suitable for reporting, than in restructuring your 'live' database and having to deal with all the resulting denormalisation issues, etc.

As stated, I believe all the information needed to generate the report is there. My point was that what is sufficient for an application is generally insufficient for basic reporting. So, when budgets get short, the database work to make queries by reporting easier is often ignored and complicated queries and processes become the norm. Those laying the problem solely at the feet of the DBA's are missing the other group that tends to make these queries necessary: App Developers. I once had an app developer tell me one type of invoice was impossible to make because some of the relationships and data were intrinsic to the application and would need to be modeled the same as the application. He wasn't far off.

There is a point between transactional and data warehousing that needs to be hit. Simple summaries or considering the question of "how do I retrieve everything in this state" will sometimes suffice. Building a system to get single transactions in an out tends to make routine report take all night or be impossible to get in a reasonable amount of time.

If your schema and app requires non-trivial SQL, then expect maintenance nightmares and lack of ability to scale.

Not always. An app requiring non-trivial SQL can be an indication of a problem requiring non-trivial workflows.

If the problem is complex, you want to model relations, not ignore them.

(But clearly not in this hairy query.)

The denormalization argument is misguided.

Denormalization is a performance consideration, and now even happens transparently at the storage layer in some RDBMs. So restructuring a 'live' database should be done for semantic reasons: because the workflow changes; because the app needs to change.

Reply on news.ycombinator.com