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.
Best Answer
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.