2

Say we have the following structure:

USER
---
USERNAME

CONTACT
---
ID
FIRSTPHONE[FK TO PHONE.ID]
SECONDPHONE[FK TO PHONE.ID]

PHONE
---
ID
NUMBER

...Now, when creating an log table logging when a user calls a contact, should it store PHONENUMBER or NUMBER? Option A:

LOG
---
USERUSERNAME
CONTACTID
PHONEID[FK TO PHONE.ID]

The argument against this is that if a change is made to PHONE.NUMBER, then the log itself will change; basically rewriting history.

Option B:

LOG
---
USERUSERNAME
CONTACTID
PHONENUMBER [Not a foreign key]

The argument against NUMBER this is that it's "denormalized".

So, which approach is better?

2

It's a business question of whether you want to know the phone number that was called or the phone ID that was called.

If, for example, you are using the log table to cross-reference with the billing from the phone company, you'd want to store the phone number that was called. If, on the other hand, you are using the log table as a CRM tracking tool, it's much more useful to know that you dialed Walter's Widgets main line rather than the particular number that was stored in the system for that contact at that point in time. Often, you're going to end up wanting to store both because you have both types of business requirements.

Realistically, I wouldn't give much thought to objections about denormalization of log tables. Worry about denormalization on your core OLTP tables. You're never going to be updating a log table, you're never going to use the log table as the source of truth for what a value is (as opposed to what the value was at a prior point in time) so there is little need to worry about denormalization.

The only real concern I'd have about denormalizing a log table is space-- if your log tables have large numbers of rows and you're storing relatively large string values that almost never change, you can end up wasting tens or hundreds of GB of space on those redundant strings and make queries against the log tables too slow to be useful. You can mitigate those sorts of things, of course, with horizontal and vertical partitioning or by maintaining separate log/ history tables for different concepts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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