This isn't exactly a mySQL-specific question, but more a design-related one.
The case is a spell-database for a roleplaying game.
The tree dsitinct entities are spell, player character and campaign, as well as some sub-entities of the spell table.
The relations are:
- a spell may be used in one or more campaigns, a campaign has several spells
- a campaign has one or more player characters, a player character may only be in one campaign
- a player character may have one or more spells, but only spells available in his campaign, and a spell in a campaign may be used by zero, one, or more player characters
The question is, how to design these relations? I have made a model using DBDesigner 4, which can be seen at
http://brunborg.errors.no/SpellsDB.png
---Ådne