Edit Question - KnowledgeHub

Title Body

When building a transactional system that has a highly normalized DB, running reporting style queries, or even queries to display data on a UI can involve several joins, which in a data heavy scenario can and usually does, impact performance. Joins are expensive. Often, the guidance espoused is that you should never run these queries off your transactional DB model, rather you should use a denormalized flattened model that is tailored for specific UI views or reports which eliminates the need for many joins. Data duplication is not an issue in this scenario. This concept makes perfect sense, but what I rarely see when experts make these statements is exactly HOW to implement this. For example, (and quite frankly I'd appreciate an example using any platform) in a mid sized system running on a sql server back-end you have a normalized transactional model. You also have some reports and a website that require queries. So, you create a "reporting" database that flattens up the normalized data. How do you keep this in sync? Transaction log shipping? If so, how do you transform the data to fit in the reporting model?

Tags (comma-separated) Cancel