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:
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
attribute can be related either to
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?
Create two connecting tables like
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_action at the same time and I do not want that.
Create columns with
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