Jump to content
  • Advertisement
Sign in to follow this  
gamedev199191

Game items and the database

This topic is 3477 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Let's say I'm programming an RPG and I am intending to store my data in the database. There's an Orc race. Now, orcs normally have: Height: 1.5 meters Weight: 120 kilograms Maximum movement speed: 3 ..and a number of other standard 'orcy' properties. I also want to have a stronger, larger, slower orc. Let's call these Orc Thumpers. I want them to have a different height, weight and maximum movement speed, but I also want them to keep in sync with the standard 'orcy' properties so I don't have to work out all the different types of Orcs and update them all when I change one of these. How would you recommend I store this data?

Share this post


Link to post
Share on other sites
Advertisement
Define a base orc class, then extend on it to define sub-classes.That should be obvious though.

Share this post


Link to post
Share on other sites
Maybe you can declare that if one of the fields in the creature's database is 0(or -1, if you want to allow 0 valued fields), than that field should be looked up at the base creature's row.



For example, if you set Orc Thumpers' height to 0, than it's height should be Orc's height.

Share this post


Link to post
Share on other sites
You do this by defining templates.

Mob templates table
(type="basic_orc", from="basic_object", height=1.5, weight=120)
(type="orc_thumper", from="basic_orc", height=null, weight=150)

On start, you load all these templates into some lookup structure keyed by 'type'.

When you create a new creature, you as for its template and read the properties from there. If a certain property is null, you ask the parent class (determined by 'from' column). You perform this lookup locally, since you've loaded all the tables already.

How to proceed from there depends. Usually you'd want objects to remain fixed once instantiated. So once you create your Orc#243 you want it to stay that way, regardless of which template it was made from. Otherwise, you need to keep track of which properties have changed. One simple way to achieve this is to use a hash map to represent the objects.

Instances are then stored into separate table, something like:
// Full state
(id=542834, type="basic_orc",height=1.5,weight=120)

Or, if you wish to use light-weight representation:
(id=542834, type="basic_orc",height=null,weight=null)

The only thing to watch about second case is that it may lead to unintended consequences. Simple example - orcs are initially immune to fire, so they are use in some lava level. But later you choose to make them flamable, so the lava level will end up devoid of enemies, since they now suddenly burst into flames as soon as they appear.

Share this post


Link to post
Share on other sites
Quote:
Original post by someboddy
Maybe you can declare that if one of the fields in the creature's database is 0(or -1, if you want to allow 0 valued fields), than that field should be looked up at the base creature's row.

For example, if you set Orc Thumpers' height to 0, than it's height should be Orc's height.


That's certainly an option. But it implies that each property stored for a creature, is stored as a column in a creatures table. Now, if I decide not to have a creatures table, and to instead have a things table, I'm then going to have a lot of fields. A large number of these which are not relevent to any given kind of thing which might be thought of and created.


CREATE TABLE things (
thingID int,
thingName string,
height float,
weight float,
movementSpeed float
)



Another option might be to have a table for properties where you only add rows for a given kind of creature when they are used. Then when you look up a property for an Orc Thumper, if there isn't a row added for that property linked to the relevant creatures row for Orc Thumpers, it would know that the parent row for Orc Thumpers was Orc and would do the look-up for that. The same sort of thing as what you suggest, but with a different database model behind it.


CREATE TABLE things (
thingID int,
baseThingID int,
thingName string
)

INSERT INTO things (thingID, baseThingID, thingName) VALUES (1, NULL, 'Orc')
INSERT INTO things (thingID, baseThingID, thingName) VALUES (2, 1, 'Orc Thumper')

CREATE TABLE properties (
thingID int,
propertyName string,
value float
)

INSERT INTO properties (1, "height", 1.5)



Now when you have all the properties as columns in a table, you can make the columns datatype the correct one for the proper values of a given property. But when you have a generic properties table, where the properties for a given kind of thing or creature are separated from the relevant row, now you have to settle for some other approach.

You can decide that you'll have a field for each datatype so that you can store values in the fields of the appropriate type. You might then register valid properties with their real datatype name in a property registration table. This means that given N datatypes, for each property value stored, you're wasting N-1 fields.


CREATE TABLE property_registrations (
propertyID int,
propertyName string,
propertyDatatype string
)

CREATE TABLE properties (
thingID int,
propertyID int,
value_string string,
value_int int,
value_float float,
)



Maybe the wasted database space is a concern, so you can decide that you'll just have an generic datatype which you'll encode property values into, like a string field or order. When you fetch the values, they can then be cast from a string to a number if needed.


CREATE TABLE property_registrations (
propertyID int,
propertyName string,
propertyDatatype string
)

CREATE TABLE properties (
thingID int,
propertyID int,
value string
)



There's any number of factors which may be worth considering.

Will the inflexibility and wasted space matter if real columns are used in the things/creatures table?

Will the space wasted matter if the properties table approach is taken?

Will the hiding of any datatype in a string mean that these rows can't be dealt with on the database layer, and instead have to be manipulated by code above it? How will that effect things in the future.

Hmm as I write this it occurs to me. Let's say you have a things table, and there's a parent thing for all creatures. And you have a creatures table, with creature specific fields. So you aren't wasting space for creature data for all things. Chances are you can introspect database internals to work out the lowest level in the chain at which a field exists. You could probably generate these tables and do all sorts of fancy things like that, keeping the full power of the database. Something none of the above solutions allow.

Anyone have any experience they can share with any of these solutions? Preference one way or the other for some reason?

Share this post


Link to post
Share on other sites
I just would do this:

CREATE TABLE properties(
propertyID int PRIMARY KEY AUTOINCREMENT/SEQUENCE/etc,
entityID int,
propertyName string,
propertyValue string
)


Property names can be whatever you need, but one would be called 'Inherit', and the value would be the entity you inherit from.

To generate all properties for an entity, you select all properties for that entity ID and store them in a map. Then, for any of the properties that are 'Inherit', you repeat the process for the associated value (ie. the 'parent' entity ID), only adding any properties you don't already have.

Somewhere in your entity class you will probably have a definitive list of properties, and that list can handle conversion from the database string to an int, float, bool, whatever. If you need ad-hoc properties that don't apply to each entity, you can just convert them at the point of use or the point of access. All it takes is about 10 lines of code to write a few conversion functions and you're done.

Share this post


Link to post
Share on other sites
Quote:
Original post by Kylotan
I just would do this:

CREATE TABLE properties(
propertyID int PRIMARY KEY AUTOINCREMENT/SEQUENCE/etc,
entityID int,
propertyName string,
propertyValue string
)


Property names can be whatever you need, but one would be called 'Inherit', and the value would be the entity you inherit from.

To generate all properties for an entity, you select all properties for that entity ID and store them in a map. Then, for any of the properties that are 'Inherit', you repeat the process for the associated value (ie. the 'parent' entity ID), only adding any properties you don't already have.

Somewhere in your entity class you will probably have a definitive list of properties, and that list can handle conversion from the database string to an int, float, bool, whatever. If you need ad-hoc properties that don't apply to each entity, you can just convert them at the point of use or the point of access. All it takes is about 10 lines of code to write a few conversion functions and you're done.


Unfortunately, I have come to the realisation that this is not an acceptable approach for me at least. It is important that I can manipulate data at the database level, and storing arbitrary datatypes as strings does not allow me to do this in a straightforward manner.

Share this post


Link to post
Share on other sites
Instead of thinking of an "Orc Thumper" as a special kind of orc, you might think of thumper-ness as a quality of said creatures - the role they play in orcish society, and the corresponding effects on their toughness as compared to normal orcs:


CREATE TABLE Role (
name string, ; e.g. 'Thumper'
heightDelta float, ; added to base role's height.
; weight can be approximated as ((baseHeight + heightDelta) / baseHeight) ** 3 * baseWeight
moveDelta int
)


Now if your normal Orcs become twice as heavy and a foot taller, your Thumpers are adjusted accordingly, and you get Elf Thumpers and Dwarf Thumpers etc. for free (although perhaps not with as much flexibility as you'd like).

Share this post


Link to post
Share on other sites
Quote:
Original post by gamedev199191
I also want to have a stronger, larger, slower orc. Let's call these Orc Thumpers. I want them to have a different height, weight and maximum movement speed, but I also want them to keep in sync with the standard 'orcy' properties so I don't have to work out all the different types of Orcs and update them all when I change one of these.

How would you recommend I store this data?

If you must use a database, then go with the simple solution - you can have your 'things' table, and if you don't like empty fields then you can give it one-to-one mappings to other tables like 'creature'(properties). Honestly, if you're just storing type information, you won't have enough data to really care about optimisation, and you'll lose some speed with the joins anyway.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!