So I made this bit of a diagram and I am not sure if I am going the right way in terms of understanding relationships. A character has attributes (Health, Mana, Power etc.) There are a quite a lot of attributes so I created a table that stores attributes and added a relation of an attribute with a character that has a value, all cool and all. Now for the IsValueType relation it gets more confusing. An attribute has a value type (Percentage, flat value etc.) so I created a IsValueType Relation that relates the attributeID with ValueTypeID, is this a one to many relationship like I drew it or is it a one to one relation ship as one attribute can only have 1 value type and 1 value type can only be linked to the same attribute (An attribute can not have 2 or more value types).
To your specific question:
ValueType can be applied to several
Attributes, but each
Attribute can have only one
ValueType, then there is a one-to-many relationship between
ValueType (the "one" table) and
Attribute (the "many" table); one
ValueType row can be attached to many
Attribute rows. So, the relationship should go the opposite direction, and the
Attribute table would include
ValueTypeID, as a foreign key to
On an unrelated note:
AttributeValue would need to be a table in your diagram. Each
Character has many
Attributes, and and each
Attribute may be associated with many
AttributeValue would need
AttributeID, and the actual
One last note: If there are a fair number of attributes that won't apply to all characters, then this design makes sense. However, if almost all attributes would apply to all characters (in the RPGs I'm familiar with, attributes like strength, charisma, mana, hit points, etc. apply to all characters), then it would probably make more sense to have the attributes directly in the
character table. If there are just a few attributes that don't apply to all characters, then it may make sense to include them in the
character table, and set them to NULL.
Using the approach you've chosen can also wind up taking more disk space; for each attribute used, you've got an additional copy of the character ID and the attribute ID to be stored. Of course, this can go the other way: if there are 100 attributes, and most characters only use 6, then the space to store the NULL values in each of the other 94 columns could easily be more than the space to store 6 extra character and attribute IDs.
Finally, how you use the data affects things as well. If you frequently need to bring up just one attribute for a character and work with that, your queries may be faster and cheaper because you don't have to pull up the
character row, just the specific
AttributeValue row. If you need to work with several attributes at once, or need other data that's in the
character table, then going with including the attributes in the
character table might be better.