-
Jun 3rd, 2022, 05:27 PM
#1
Thread Starter
Addicted Member
When to denormalize?
I have my databased normalized like crazy and I've been thinking about histories. I already denormalize prices when a transaction occurs (store the price itself, not a reference to the price). Prices will change over time and I don't want to see sales from last year with this year's prices.
It also occurs to me that maybe I should be denomalizing other fields like the product names in case that get removed or changed in the future too. This morning I thought about just completely denormalizing everything in a table that will be basically a history - nothing in the table will change in the future anyway. If I need to get data I can work with strings etc without to much problem (just sacrifice speed).
What do you think?
-
Jun 4th, 2022, 05:57 AM
#2
Addicted Member
Re: When to denormalize?
G'Day pmeloy
Are you wanting to do analysis of your system in the future, if you think there is even a chance you will need to do this.
I wouldn't denormalize your current data because you will always need the functionality that norm... allows for.
Just a few new 'history' tables that are denor...
-
Jun 4th, 2022, 07:23 AM
#3
Re: When to denormalize?
Originally Posted by pmeloy
What do you think?
This is not denormalization. This is what getting business requirements wrong looks like :-))
I highly doubt the original business requirements would allow changing product name or client name in the nomenclature tables would affect past documents so that invoices from last year cannot be printed carbon copy. That would be insane :-))
Denormalization is when you add columns for performance reasons (not for correctness reasons) either skipping a join (not very effective honestly) or skipping an aggregate (this is most effective).
For instance keeping SUM of remaining payments on outstanding documents issued to a client in clients table would be considered denormalization which is very performance beneficial for reporting purposes. Will slow down document creation but will speed up credit limit check and/or other reports.
cheers,
</wqw>
-
Jun 4th, 2022, 04:18 PM
#4
Thread Starter
Addicted Member
Re: When to denormalize?
Yes current data would all be kept normalized of course (too hard and slow to keep it denormalized) but I'm thinking that each month or each year I should transfer old transactions into non referential history tables. I can still do quite a bit with strings instead of IDs but do I need to have a lot of detail when looking at March 10th sales from last year? Right now a transaction has one table for a transaction header and another table with a single row for each item in that transaction. When looking at something last year I'm thinking just one table with totals and no details about specific items. Or maybe still two tables but everything using strings and values.
-
Jun 4th, 2022, 06:52 PM
#5
Re: When to denormalize?
Originally Posted by pmeloy
Yes current data would all be kept normalized of course (too hard and slow to keep it denormalized) but I'm thinking that each month or each year I should transfer old transactions into non referential history tables. I can still do quite a bit with strings instead of IDs but do I need to have a lot of detail when looking at March 10th sales from last year? Right now a transaction has one table for a transaction header and another table with a single row for each item in that transaction. When looking at something last year I'm thinking just one table with totals and no details about specific items. Or maybe still two tables but everything using strings and values.
Sounds like a data warehouse to me. Denormalized data with pre-built/calculated values for reporting.
-tg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|