Need some advice in DB design
View Single Post
01-28-2008, 05:35 PM
Join Date: Jan 2008
Location: Maine, USA
My suggestion: Create the item table like Alan says, and then you could have a different table for each item type containing item properties. Having multiple property tables may help keep the table size down thus keeping query speed up.
Be careful in those property tables. The problem with having a defined number of fields in a table is that...a defined number of fields.
So, for instance, you wish to add or remove a property to an item, you have to change the structure of the whole table, and then update any and all queries that would reference this property.
Something I try to do when confronted with something like this is to have a table set up something like this:
item_id - id of the item (links to the main item table)
item_property - name of the property
property_value - value of the property (such as HP or whatever)
item_id item_property property_value 1244 prop_hp_total 2455
This way you never run out of property names, because you just add one with the item's id num. Queries are easier to write as you can just return all of the results for a given item and display accordingly. Actually maintaining the queries is not an issue anymore, because properties added or removed are not really an issue.
Hope this helps!
Why is it drug addicts and computer aficionados are both called users?
" -Clifford Stoll
The Following User Says Thank You to buggabill For This Useful Post:
View Public Profile
Send a private message to buggabill
Find More Posts by buggabill