For example, there are three tables: players
, dungeons
, and instances
.
A player
has many dungeons
and each dungeon
has many instances
, like in a well-known game of World of Warcraft. Each player
would have a dungeon_id
as a foreign key and each dungeon
would have an instance_id
as a foreign key. That would be a sufficient minimalist approach. A player
of each instance
can be accessed through the dungeon
table. This would result in 1 less column, but a bit longer query.
Another approach is to add player_id
to instances
table as a foreign key. Now players can queried directly from instances
table. Shorter query, but one more column for a foreign key.
Which approach is better in the long run? In practice it seems like adding a foreign key everywhere where it is logically possible makes it easier. But then wouldn't it result in tighter coupling and make code harder to refactor?