1

I have a normalized relational database. I often find I need traverse multiple tables to get the related object of a related object and so forth.

So by that I mean let's say I have related tables A->B->C->D. In a process that uses both A and D, this might be a costly traversal to go from A to D. I'm thinking of adding a second foreign key on A whereby I have A->B and A->D. The only issue I see here is potential inconsistency of the keys (what if the B value is updated but not D).

What are the pros and cons and considerations of doing this sort of double linking?

Some specific context: I'm using the Django ORM and postgreSQL for database operations.

  • 1
    this might be a costly traversal - have you measured it? This may be a case of premature optimization. – Dan Pichelman Jun 14 '18 at 19:47
  • @DanPichelman measuring is indeed on my list. I read the practically the same comment on a similar post a while back (might have been you) and I always keep that in mind. – Jad S Jun 14 '18 at 19:51
  • I've probably made that comment once or twice :-). To me, the cost of resolving the inconsistency (which will occur - programmers aren't perfect) more than likely outweighs any benefit gained. You situation may be different. – Dan Pichelman Jun 14 '18 at 19:58
  • Yea, I guess what I'm trying to get out of this question is an idea of whether this kind of "shortcutting" is common practice or whether it's more commonly avoided – Jad S Jun 14 '18 at 20:02
4

The pros of this approach are:

  • the ease of a direct access to the connected object if you don't need the objects in the middle;
  • potential performance improvement due to the reduction of joins (but with the optimizing engine of modern RDBMS, this gain could be marginal)

The cons are all the cons of denormalization:

  • you have redundant data
  • you have to keep this data in sync. The more objects there are in the middle, the more difficult it gets to keep the sync.
  • Creation of concurrency bottlenecks: every change on objects of the middle might require an update of the A object, which can cause locking issues if different processes/users are at the origin of the change on the intermediary objects.
  • Complexification/duplication of code: e.g. if B has some kind of status (e.g."DELETED", "SUSPENDED" or "INACTIVE) that impacts the accessibility of C and D for A, you'd need to replicate this feature in the code that maintains the sync. SO you need to analyse not only the technical relations between the data, but also the business logic that is behind it.

Finally, before considering the denormalization,you need to analyse the relation between A and D, to know where the foreign key needs to be added. If it's 1 to one you may add it in A (but does D already exist when you create an A?). But if it's one to many, you can't consider duplicating A rows, so you'd need to add a foreign key back to A in D (same question: does the A generally exist when D is created ?).

Personally, I'd fear that the cons of the denormalization outweigh the pros, especially when thinking about concurrency issues But a more in-depth analysis with measurements is required to analyse the dynamics behind the tables (e.g. number of reads vs. number of writes) in order to know the objective facts.

  • Yes it from your breakdown, it sounds like your conclusion is spot on. I was worried about performance issues but if they can be optimized out then I'm with you. – Jad S Jun 15 '18 at 16:47

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.