For example, there are three tables:
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
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?