Mysql – Table with relation to two others (three-way relation?)

eavforeign keyMySQLrelations

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

Best Answer

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.