Do I understand this relationship correctly?

Question

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).

Answer 1

To your specific question:

If a 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 ValueType.

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 Characters. AttributeValue would need CharacterID, AttributeID, and the actual AttributeValue.

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.