Right i have a problem or two with my db design which i will try
to explain:
1 Player can have many Item Instance.
1 Item can have many Item Instance.
Problem 1.
Now i have subtypes of Item, such as Radio, chair, bed etc.
I was thinking that perhaps the best way of implementing this
would be to as such:
Item: ItemID, name, description, any other common attricutes.
Radio: ItemID, frequency, other radio specific attributes
Bed: ItemID, bed width, bed height, other bed specific attributes
etc.
Now the problem i have is when accessing an item which
type i do not know, so i could:
In the Item table add an attribute called "type" which
could hold the string of the table name to look in.
Yet that type field should not be there according to database
design principles, correct.
Is there a correct way of implementing subtypes (other
than putting everything in the Item table and allowing nulls?
Problem 2.
Item instances:
In Item Instance table i would need to store InstanceID, PlayerID
and ItemID.
Say an Item_Instance row represented a Radio (Item subtype).
This radio has a frequency attribute.
How would i enable the ItemInstance of this Radio (so the data
in ItemInstance might look like "1, 2, 3" assuming playerID 2 and
the Radio has an ItemID of 3) to have a different frequency to the
one defined in Radio table? I would want to do this for example,
if player with id 1 changed the frequency of the radio to 20.02MHz.
Would i simply copy all changeable fields from all Item subtypes into
ItemInstance?
Thanks for any help.