I stumbled across a weird situation that one table has relation to two others but record in it can only have a relation to one of the tables. To clarify things, I have tables: attribute, visit, visit_action. visit is a parent of visit_action - on one visit a user can perform multiple actions. Both visits and actions have attributes that are made of name and value. One attribute can be related either to visit or visit_action but never to both at the same time. One visit can contain of multiple attributes and so does action. How should I keep those relations in database?

Option 1 Create two connecting tables like visit_attribute and visit_action_attribute both with relations to attribute but one with relation to visit and one to visit_action. The problem I have with this solution is that a attribute can end up being tied up to both visit and visit_action at the same time and I do not want that. two connecting tables

Option 2 Create columns with id_visit and id_visit_action directly in attribute table. It seems like a good fit because attribute can't be related to many visits or actions but there is still problem that it can end up being tied up to one visit and one action at the same time. It feels bad that too one column in attribute table will always be NULL (either id_visit or id_visit_action). connections in attribute table

  • I went with option 3 as thanks to that I will be sure that each attribute is connected only to visit or visit_action and not both, thank you @RDFozz.
    – nass
    Feb 17, 2018 at 15:41
  • EAV schema has a lot of problems.
    – Rick James
    Feb 21, 2018 at 17:11

1 Answer 1


There is a third option:

Create tables visit_attribute and visit_action_attribute with name and value columns in each (and drop the attribute table altogether).

This removes the possibility of a given attribute being linked to both a visit and a visit_action, with no need for id columns with NULL values.

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.