Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: When denormalize is preferred instead normalization?

news.ycombinator.com

Post reply on HN
Print view View original

11–19 of 19 posts

Re: Ask HN: When denormalize is preferred instead normalization?

#11

Post by iman453 »

bad_username wrote: post #4

It is generally fine to denormalize the storage of immutable data points. I mean, if you have something that can never change or cease to exist, it's OK to store it in many places by value, for performance or convenience reasons. If the value can be changed or deleted, it's a whole different story. Denormalization of a data point is isomorphic to caching it (you store it nearby to avoid getting it from far away). And…

Never thought of denormalization as caching. Makes sense. Thanks for the perspective

Re: Ask HN: When denormalize is preferred instead normalization?

#13

Post by iceporter »

CodeWriter23 wrote: post #7

NetSuite uses the “child” table model though mostly normalized. transactionline for the “human” details transactionaccounting for the GL. Either of these tables can be joined on the transaction.id field and the sequence numbers of both correspond to each other. But like I said, normalized pretty much to 3rd normal form. You have to join to get the “description”. There is duplication in the quantity and rate are in tr…

I'm really interested in this, pardon my english understanding.

did netsuite copying value from transaction for transactiononline or transactionaccountingline?

Re: Ask HN: When denormalize is preferred instead normalization?

#14

Post by iceporter »

iman453 wrote: post #11
bad_username wrote: post #4

It is generally fine to denormalize the storage of immutable data points. I mean, if you have something that can never change or cease to exist, it's OK to store it in many places by value, for performance or convenience reasons. If the value can be changed or deleted, it's a whole different story. Denormalization of a data point is isomorphic to caching it (you store it nearby to avoid getting it from far away). And…

Never thought of denormalization as caching. Makes sense. Thanks for the perspective

some people use mongodb or redis for caching a query result

Re: Ask HN: When denormalize is preferred instead normalization?

#16

Post by iceporter »

beckingz wrote: post #6

Create a view in the database that takes the transactions and returns the data in the format that you want. It will probably have a bunch of JOIN client.id on transaction.client_id etc etc, but it's the easiest way to get started. When the view stops running fast enough, switch to a scheduled materialized view.

I think I will use this approach

but I think I need to use union to make a single table that is combination of accounting journal tables and view of transaction tables

Re: Ask HN: When denormalize is preferred instead normalization?

#17

Post by iceporter »

drzel wrote: post #2

The copied data will one day get out of sync. And you will have a difficult and urgent issue to solve. Query complexity on the other hand requires knowledge, to make the query work and again to make it fast. But it won’t be urgent, and having made it work you will be a better developer. Of course, sometimes we need to copy / cache etc. but avoid it if you can.

yeah you are right I think I will try to use view, and union

this will make me a better developer!

Re: Ask HN: When denormalize is preferred instead normalization?

#18

Post by vivegi »

The general guidance is that you segment your application domain into two categories - Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP).

The OLAP data is populated from the OLTP data using queries (snapshot tables, materialized views etc., could be the implementation).

You then add/refresh data into the OLAP tables in a set frequency (for eg: daily, weekly, bi-weekly, monthly, quarterly, yearly etc.,).

The OLTP system has up-to-date realtime transactions. The OLAP data has snapshots as of a particular date. The OLAP data may be denormalized while the OLTP data is highly normalized. This makes the OLAP data optimized for reads while the OLTP data is optimized for writes.

Re: Ask HN: When denormalize is preferred instead normalization?

#19

Post by CodeWriter23 »

iceporter wrote: post #13
CodeWriter23 wrote: post #7

NetSuite uses the “child” table model though mostly normalized. transactionline for the “human” details transactionaccounting for the GL. Either of these tables can be joined on the transaction.id field and the sequence numbers of both correspond to each other. But like I said, normalized pretty much to 3rd normal form. You have to join to get the “description”. There is duplication in the quantity and rate are in tr…

I'm really interested in this, pardon my english understanding. did netsuite copying value from transaction for transactiononline or transactionaccountingline?

It does the quantity x price from the transactionline and stores that in transactionaccountingline. So for example, if you edit a transaction, the code accepting the POST has to update two records per line, not just one.

Unless of course the extension field in transactionaccountingline is seeded by a SQL Function that does that automatically. Which might be a preferred approach for your implementation. The downside of SQL functions though, they tend to be invisible to others maintaining the code.

Post reply on HN