1

We are designing a system, in which we need to store amount of SalesTax applied as well as Tax percentage value. We decided that we will keep tax value in separate table (simplified example):

Taxes table:

+----+---------+---------------+------------+------------+
| Id |  Area   | TaxPercentage | StartDate  |  EndDate   |
+----+---------+---------------+------------+------------+
|  1 | Japan   | 5%            | 2019-01-01 | 2020-12-31 |
|  2 | Japan   | 6%            | 2021-01-01 | null       |
|  3 | France  | 21%           | 2019-01-01 | null       |
|  4 | Germany | 19%           | null       | 2022-12-31 |
|  5 | Germany | 18%           | 2023-01-01 | null       |
+----+---------+---------------+------------+------------+

and then we have purchased history table:

OrdersHistory table - option 1:

+----+------------+---------+----------+------+--------+---------+
| Id |    Date    | OrderId | PriceVal | Curr | TaxVal | TaxPerc |
+----+------------+---------+----------+------+--------+---------+
|  1 | 2020-11-11 |     123 | 100.00   | YEN  | 5.00   | 5%      |
|  2 | 2020-11-12 |     456 | 200.00   | EUR  | 28.00  | 19%     |
+----+------------+---------+----------+------+--------+---------+

OrdersHistory table - option 2:

+----+------------+---------+----------+------+--------+-------+
| Id |    Date    | OrderId | PriceVal | Curr | TaxVal | TaxId |
+----+------------+---------+----------+------+--------+-------+
|  1 | 2020-11-11 |     123 | 100.00   | YEN  | 5.00   |     1 |
|  2 | 2020-11-12 |     456 | 200.00   | EUR  | 28.00  |     4 |
+----+------------+---------+----------+------+--------+-------+

The difference is in last column.

In other words - one developer claims we should keep history table (ledger) in denormalized form (TaxPercentage is value object, thus it should be copied) while another developer claims that database (tables) should be in normalized form (thus we should keep TaxId reference to Taxes table). OrdersHistory table can have 100.000s or millions of records.

Which approach is better and why?

1
  • There may be laws that determine how such records should be stored. Also, in general, a supposedly "normalised" form of storing accounting records often frustrates archiving of old data, reduces performance and scalability, and destroys the natural audit trails and reliability of old data. The reason double-entry systems were invented wasn't because someone failed to realise that it could be "normalised" into single-entry.
    – Steve
    Commented Mar 3, 2020 at 19:23

1 Answer 1

2

It really depends on the type and volume of queries you are going to run against this DB.

More precisely it depends on if you will ever need a JOIN between OrderHistory & Taxes if you have an explicit TaxPerc field in OrderHistory. Area seems like it may be valuable for some queries for example, although that assumes there is not another table where Area is readily accessible with a JOIN from OrderHistory.

Also, I'm guessing that Taxes will be a fairly small table, so it's not going to be much of a overhead to do an actual JOIN for each query.

Be careful here, if these were your only two tables and you went with TaxPerc over TaxId, it could be impossible to get from OrderHistory to Taxes if you had requirements for a new query requiring a JOIN in the future (e.g. if Spain and Greece actually had the same Tax Percentage as they also have the same currency).

There is a third option (which will probably make both developers equally unhappy), have both fields in OrderHistory.

NB The following figures are for arguments sake, you'd actually need to investigate if this example brought tangible benefits for your particular situation.

If you had 10M queries per day requiring fields

Date | OrderId | PriceVal | Curr | TaxVal | TaxPerc

and 100,000 queries a year requiring fields

Date | OrderId | PriceVal | Curr | TaxVal | TaxPerc | Area

then it might actually be worth having an OrderHistory table like this

| Id | Date | OrderId | PriceVal | Curr | TaxVal | TaxPerc | TaxId |

as you could save more on processing than you'd lose on storage and could have faster responses for the 10 M a day queries.

Denormalization is OK if it brings value.

Denormalization was originally driven by the high cost of storage. Storage is relatively a lot cheaper these days, this is one of the reasons why we have seen the rise of NoSQL databases.

We still shouldn't be wasteful, but we should evaluate the benefits of denormalization if it saves on response times on queries and processing in the database engine.

Update In response to some comments about value in other contexts..

Simpler Rows

Simpler rows in terms of storage? I think I covered that. In terms of human readability, then it's only really DBAs or devs building integrations reading database tables at that level and I don't think denormalization or using one attribute/key over another would cause them too much of a headache.

Simpler Queries

In SQL like queries, those that only look at one table are the simplest and also the fastest. But a simple JOIN operation isn't going to give your DBAs/devs nightmares (or at least it shouldn't). If the query is very complex with multiple JOINs, then that can start causing pain. Denormalization can help reduce JOINs, but at the cost of storage, read time (if every field isn't required for every query) and potentially a slew of different indexes. At that point, if your numbers are high enough, it's time to start considering separate read models.

3
  • Thanks. You focus on value in terms of performance. How about value in terms of simpler rows, simpler queries, immutable read-only rows? Commented Mar 4, 2020 at 9:01
  • @MaciejPszczolinski I've updated my answer in regards to the value of simpler rows and simpler queries. I haven't added anything about 'immutable read-only rows' as I am not sure what you are asking. If something is immutable, it can only be read-only, but I'm still not sure how that relates to your question or my answer.
    – K Mo
    Commented Mar 4, 2020 at 16:59
  • by "immutable rows" I meant that if order happen in given moment of time, it's tax is known (e.g. 5%). If we put this tax in Order's row in denormalized way, we are good. If we keep it as reference to another table, then we have to make sure that the referencing table is ALSO immutable - otherwise, change in other table can impact ledger of Orders. If referencing table record mutates, it could change a tax of our already-done order (e.g. from 5% to 7%) which will result in corrupted data. Commented Jun 13, 2020 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.