1

I've recently seen the following schema and was wondering what the reasons behind it could be. Let's suppose we have three tables A, B and C.

  • A table has one to many relationship with B, so B has an A_id.
  • B table has one to many relationship with C, so C has a B_id.

Looks pretty logical so far. However, in the schema I saw C also had A_id as well as B_id. The question is, what could be possible reasons to do so?

The only one that comes to my mind is to speed up the query to get all C entities which belong to A, though I am not sure it's that significant. Has anyone also used the aforementioned schema and why?

The question might seem broad so I'm not sure if it's really Q/A format. I couldn't find any information to read about this design decision since I don't know whether it has a name. If it has, I'd appreciate if someone told me how it's called.

6
  • 3
    There is nothing wrong with the design, but without knowing the actual entities and their relationships, it's going to be hard to answer. One possibility is that you can have C entities without needing a B entity. Is there an actual FK constraint in place? Does B/C entities allow for NULLS in the A_id and B_id fields? Feb 28, 2017 at 14:26
  • It's not possible to have C without B, so each C who has A, also has B. There are no database constraints themselves, but it's not possible due to the business logic. So, C is obliged to have B and B is obliged to have A. Feb 28, 2017 at 15:21
  • 1
    Well, if the database isn't constraining it then it is at least theoretically possible for there to be orphaned C's. But if the application is maintaining the referential integrity then I bet that's why there is an A_id for C entities, it makes it easier on the developers. Are they using an ORM, by chance? Feb 28, 2017 at 15:27
  • 1
    One of the reasons might be if you need to enforce uniqueness of some attributes in C for a_id.
    – a1ex07
    Feb 28, 2017 at 15:52
  • 1
    I have seen a similar structure, where the A_id in C indicated the A_id at the point this C row was added. If B is moved to a different A, then the A_id as of the point of creation of C would be lost. If knowing the A row this C row was associated with at the point of creation is useful, then this is one way to retain it. Of course, without knowing more detail, it's difficult to know how it was intended to be used.
    – RDFozz
    Feb 28, 2017 at 15:55

2 Answers 2

1

This could be to resolve a data model issue known as the FAN TRAP. There is quite a good youtube video describing it at https://www.youtube.com/watch?v=YKqJ_FjfyVM. The video also goes on to describe another common modeling issue; the CHASM TRAP.

1
  • Thank you, a really interesting information I haven't encountered before. Mar 13, 2017 at 16:05
0
  • A is a customer of insurance company.
  • B is some item or property A owns.
  • C is an insurance.

C could be a fire insurance for a house (B) or traffic/collision insurance for a car (B), in which cases C links to B, or a life or health insurance for the person himself (A), in which case C links to A.

In this scenario, some kind of type field of C would indicate to data model or application logic, should it link to an A or a B.

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.