Game items and the database

Define a base orc class, then extend on it to define sub-classes.That should be obvious though.

Quote:

Original post by suika Define a base orc class, then extend on it to define sub-classes.That should be obvious though.

In the database, not as code.

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.

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.

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.



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.



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.



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.



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?

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.

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.

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

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.

Quote:

Original post by gamedev199191 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.

If you want strict data typing for each property then you're going to need the same structure for any modifiers you wish to apply. I'd suggest a table with almost the same schema as your normal things, called 'overrides', with NULL values in the columns where you don't want to change the inherited value. You can add an extra column for an 'inherits from' ID to refer to the original thing that you're overriding, or you might have a 3rd table which maps overrides to things, etc.