Database Build - Game concept / inventory management

[Misticism]

I've posted this in the MySQL forum but I have recently stumbled across this forum and it looks far more active so I'm hoping for a response here:

Some notes: MySQL database, MyISAM type (debatable, I don't know why I would use InnoDB or how it differs from MyISAM, but the book I am learning from uses MyISAM, granted it is an older book.)

Hey guys, I hate to be the nubcake but I am. So I am still learning php and as a side project as I learn skills I intend to build an online game. Obviously, this needs to be database driven. This is about the time I learned about normalization. Then I started to normalize my data and got even more confused. Note, current concept of the game doesn't involve any real strong user to user interaction.

Needs of my particular Database:
Hold user information (name, password, email)
Link that user its various creatures it owns
Link that user to it's inventory (which should be easily changed)
Link the various creatures to its equipped inventory
Hold the various creatures statistics

so far I've gotten as far as this:
USER
user_id*
user_name
user_pass
user_email

USER_STATS
(perhaps this and the table above could be merged? I imagine additional statistics will eventually be added to each user, would it really make any difference to have the tables merged? )
user_id*
user_level
user_health
user_alignment

CREATURE
user_id* (according to the laws of normalization this seems wrong because there would be lots of redundant data here. Is that right? Users can own multiple creatures)
creature_id*
creature_name
creature_type
creature_slots (number of inventory slots)

CREATURE_STATS
(Again, perhaps this and the table above could be merged? I imagine additional statistics will eventually be added to each creature, would it really make any difference to have the tables merged? )
creature_id*
creature_level
creature_str
creature_dex
creature_luk

USER_INVENTORY (unequipped items, and items that don't equip at all)
user_id*
user_gold (holds number of how many this user has)
user_healthpots (holds number of how many this user has)
user_ressurectpots (holds number of how many this user has)
(not sure how to manage the above table because there are going to be items that some users may never have access to. So it might be nicer to have item_ids, but I need to link that also with how many of each, each individual user has. Also, each user may have none, or many of the various items that can be acquired. So having a very large empty table may not be the best way to approach this. Also I would think that as the game grows, new items will be available to users (as I grow the game). If I were going to have an unset number of unique items for each user and be able to track which items and how many of each, each user individually owns, how would I approach that?)

CREATURE_INVENTORY
creature_id*
user_id*
(I'm thinking it doesn't actually need the user_id* column since the creature table links the user and creature, and there would be redundant data here anyway)
creature_slot1
creature_slot2
(I'm struggling with how to manage this because not all creatures will have the same amount of inventory slots, and I would like the ability to upgrade the number of carrying capacity as they level up. This table has the same conceptual problem as the user inventory as I don't think making a column for each "possible" item hold the equipped number for each creature is the right solution. But again, I don't know a solution to make this more streamlined.)

So there are my thoughts and I hope it all makes sense, if you have any questions for me to answer to allow you to better understand my current database structure let me know. Any help will be incredibly appreciated.

After the original posting I had some thoughts later that night:

Upon further thinking about this database I believe the user_id foreign key should be removed from the CREATURE and CREATURE_INVENTORY tables and a new table USER_CREATURE_LINK should be created as follows:

USER_CREATURE_LINK
user_id*
creature_id*

Does that seem like a good idea?

I'm still on the ropes about merging USER and USER_STATS together and CREATURE and CREATURE_STATS together. Thoughts and suggestions?

What are best practices for that kind of thing? Users will have different stats then creatures so USER_STATS and CREATURE_STATS can't merge together.

Also still don't know how to manage the inventory of the user and creatures best.

Thank you for your time!

[Simon Tite]

You are definitely right about the USER_CREATURE_LINK file, this is a classic normalization technique.

Seems to me you should use the same technique for the inventories, i.e. an inventory items file (all the possible items and their descriptions), and a USER_INVENTORY_LINK file containing the user id, the inventory id, and the quantity held. When normalizing data, if you find yourself thinking about putting a fixed number of slots in a record, this usually means that there should be a separate table for them.

But then, what about the creature inventory file? Well , why does this have to be a separate file - you may say that creatures and users cannot have the same inventory items, but that is just a rule of the game, and can be catered for by a flag in the inventory file (U/C/B - applies to user, creature or both).

Similarly with statistics: you could have a STATISTICS file, a USER_STATISTICS_LINK and a CREATURE_STATISTICS_LINK, and a simple flag on the STATISTICS file, user, creature or both.

Thinking further, what is the difference between a statistic and an inventory item? A character can possess 10 pieces of gold, 100 strength points, 1 sword, 90 health points, 10 magic beans... you see where I'm going with this? If the game needs to apply certain rules to certain inventory items, then use a flag to identify what rules apply, e.g. INV_TYPE (options "STATISTIC","WEAPON","MONEY","FOOD"...). If things have broadly similar properties, then they really belong in the same table.

Even further, what is the difference between a user and a creature? OK, philosophically there's a great difference, but in terms of your game users and creatures have very similar properties, they both have inventories and both possess attributes or statistics. It's only the rules of the game which suggest that a creature can't own a user, or a user can't travel far without a horse.

So, I would have a USER file, just to hold the user's out-of-game information, email, etc. A CREATURE_TYPE file which is a list of available creatures in the game, one of which might well be "USER". A CREATURE file containing all the creatures currently in the game. An INVENTORY_TYPE file, which is a list of all available and quantifiable items that a creature might possess. A file to link creatures to their inventories. A file to link creatures to other creatures they possess. (The rules of the game may disallow slavery, but the database doesn't).

Here's a schema I worked out as a possible starting point. It probably isn't perfect, or the most efficient - often normalization can cost in terms of efficiency, by that I mean that sometimes duplicating data in a table can be a *good* thing, avoiding having to do multiple joins. But then again, a good RDBMS should be able to optimize things so that the efficiency gain wouldn't be noticeable.

Note that I've adopted a naming convention for fields - this is just a convention I use, I'm not recommending it as good practice, though some sort of convention probably is good practice.

I would start as you do with the user, who simply needs to be identified to the system.

USER (abbreviation US)identifies a human user of the system
----
usid*
usname
uspass
usmail

Now it seems to me, that in your scenario, both users and creatures can possess inventory items and statistics quantities - they may not be the *same* statistics or inventory items, but all that is just a matter of the rules of the game: I see no real difference between a "user" and a "creature" in terms of information kept. So my next file would be:

CREATURE (CR) identifies individual instance of character in the game.
--------
crid*
crusid (the user id, if it's a real user, otherwise NULL or blank or 0)
crname (maybe not necessary if it's same as the type description)
crctid* (creature type id)

CREATURE_TYPES (CT)
--------------
ctid*
ctdesc

USER_CREATURE_LINK (OW) links one user to creatures s/he owns
------------------
owowid* (the owner)
owcrid* (the creature he owns)

INVENTORY_ITEMS (IN) all possible "things" a creature can possess (apart from other creatures? -- maybe more normalization is possible here?)
---------------
inid*
inname (e.g. gold, sword, bucket, etc)
inucb (applies to user, creature or both?) - perhaps a list of creature types?
intype (e.g. money, weapon, statistic?)

Now I'm thinking about statistics and inventory: what is the difference between "user 12 has 30 pieces of silver", "creature 11 has 50 strength", "user 10 has 95 health points"? Well, not much really. So I would combine the statistics and inventory into one table, and just use flags if the game rules required different treatment of different sorts of inventory. For example, health points (presumably) can't be bought or sold, you can't use gold as a weapon, etc.

INVENTORY_LINK (IV) links user or creature to an inventory
-------------------
ivcrid* (id of creature that owns it, can be a user, see above)
ivinid* (id of the item or statistic owned)
ivqty (how much or how many is owned)

[Misticism]

My concern with heavy use of flags is that your trying to merge a lot of information into one table and use flags instead of table names. I'd imagine this would ultimately lengthen query times When I know I can select from user_stats and know I'm getting only user stats vs selecting a large table then having to run a second check on the flag. I do understand the concept and may use the flags eventually, but not in place of a table. I'm new to MySQL but Im pretty sure I should set up tables that allow MySQL to do it's job efficiently.

Secondly, discussions at another forum convinced me that a user_creature_link is unnecessary. One user can own many creatures, but a single creature can't be owned by many users hence a one to many relationship instead of a many to many relationship (when linking tables are necessary)

For the inventory however I have currently decided on this model (subject to change)

USER_INV
user_id
item_id
item_qty

CREATURE_INV
creature_id
item_id
item_qty

INV_KEY
item_id
item_name
item_desc

We also discussed merging user_inv and creature_inv since the tables are almost the same. But decided not to because we can make sure that the combination of user_id and item_id is unique.

Example entry:
user_id: 1
item_id: 1
item_qty: 3
(1, 1, 3) the first two values are 1, 1 so, that[\B] combination of values can never occur in the table again.

And the guys helping me out mentioned that some databases don't really like NULLs in unique values. So if I also had creature_id in that table tracking user items and creature items you would need to have the combination of user_id, creature_id, and item_id as a unique set. But sometimes the item is for a creature sometimes for a user and there would inevitably be NULLS in that combo. Yes I could put in the user with all the creatures and use flags to delineate, but again why tables can be used to delineate?

Thank you for your input though, and I would like to hear your thoughts on what I just discussed to see if you agree or not and why. I think flags can be useful, but should be used in moderation personally.

[Simon Tite]

discussions at another forum convinced me that a user_creature_link is unnecessary. One user can own many creatures, but a single creature can't be owned by many users hence a one to many relationship instead of a many to many relationship (when linking tables are necessary)

You're quite right about the user_creature_link, it is unnecessary, and I hadn't considered that it was a one-to-many relationship.

And the guys helping me out mentioned that some databases don't really like NULLs in unique values

Well they may have a point, but nowhere in my proposed schema did I suggest that NULL should be used as a unique key value. The only place that I used user_id, was in the creature file, where I included "crusid" as the user's id, only if this creature was associated with a real user. It would not be part of the key to the file, and is just additional information about the creature which strictly speaking is not necessary at all.

My concern with heavy use of flags is that your trying to merge a lot of information into one table and use flags instead of table names

Not exactly, I just believe that normalization implies that one table should contain information about one specific object: the "flag" is just a piece of information, just as is name, address, creature type, etc. I disagree with you that flags are a bad idea. ("Flag" is probably a bad name though). Say I have a client database, and I have to treat my clients differently if they are domestic customers or foreign customers. Does it make more sense to have two tables, "DOMESTIC", "FOREIGN" containing similar data (name, address, balance, link to orders table), or do I use a flag, "D/F"? If I choose the first, what happens when I realise that all my foreign customers are from the EU, and suddenly I get an order from the USA where the rules are completely different? Do I create a new table, "USA"? Or just allow the flag to take the value "D/F/U"? And what if one of my EU customers moves to India?

I'd imagine this would ultimately lengthen query times

Don't try to second-guess the database engine! If you have millions of creatures and only thousands of users, then perhaps you might shave off a few nano-seconds by querying SELECT quantity FROM user_inv WHERE user_id=12 instead of SELECT quantity FROM creature_inv WHERE creature_id=12345 AND creature_type="human" , but hey, let's not get obsessive about it! (and of course if creatures and users were in the same file, they would all have unique creature_id's, so the time difference would be due to looking in a file of a few thousand records as opposed to a few million. Database engines are GOOD at that sort of thing, and you wouldn't notice the difference, believe me.)

I think the clue is in your phrase "..but again why tables can be used to delineate?" - if you are using table-names to delineate between objects which are more or less the same thing, where do you stop? Would you have a table for "GOOD_CREATURES" and one for "BAD_CREATURES"?

In a way, I think perhaps my view of the game differs slightly to yours. I see such a game as a world populated by CREATURES, most of which are operated by the computer program, but where one or more of them may be operated by one or more logged in users. The commonality of all creatures is that they can move around independently, act independently, own objects (and other creatures), dispose of or use objects, etc. In other words, all creatures are generally subject to the same laws of the universe (or rules of the game), but their abilities are constrained or enhanced by the objects they possess and the type of creature they are. The only difference between a user and a creature, is that one is controlled by a human being sitting at a keyboard, the others are controlled by the computer program.

Also in this world are objects, or INVENTORY items. These have no independent life of their own, but act as enhancements (or the opposite) of creatures' abilities. They can be owned by creatures, or just left lying around, but in general they don't do anything much by themselves.

As far as it goes, that seems to me a good scheme, as does yours so far, but in my opinion, keeping commonly held information in one table leaves a lot more room for future extension, which also means that some decisions can be postponed until the prototype is working. Consider, can a user operate more than one character? Can a user play the rôle of creature, like for example a dragon, or a troll? (I don't know if that's the sort of game you are aiming for, but decisions can be postponed if you don't limit yourself too much by saying things like, user and creature inventories are different, or that users can possess strength and health, but creatures can possess dexterity and luck). Any such limitations are rules of the game, and should not affect the design of the database.

Hears my second stab at a schema (* is the unique key field, ** means it's autogenerated when the record is created):
CREATURE (cr)
-------------
crid** - uniquely identifies a creature in the game, human or not.
crname - its name, eg "Fido", "Hagar the Horrible", could be blank
crtid - its type, refers to the creature_type file
crowner - the id of this creature's owner, if any. Can be null.

CREATURE_TYPE (ct)
------------------
ctid** - uniquely identifies the type of creature
ctdesc - description of creature ("dragon", "horse", "human", "dinosaur"..)
.... other properties which might be relevant to a creature, probably including a "flag" to indicate whether a user controls it, or whether the game's programmer controls it. Can it fly? Can it fight? Can it be owned? Some properties could also be default values, such as average speed, initial health, etc, etc, etc

ITEMS (it) - all possible inventory items allowed in the game
-------------------------------------------------------------
itid** - unique identifier of the items
itdesc - description of the item ("gold", "sword", "strength", "health"...)
....other properties which might be relevant to an item, possibly including flags to indicate, is it a weapon? can I eat it? what units is it kept in (tons, pieces, lbs?)
can it go negative?

CREATURE_INVENTORY (ci)
-----------------------
cicrid* - the id of a creature
ciitid* - the id of an item (these two items form a unique key, neither of them can be NULL, but they are not autogenerated)
ciqty - quantity of the item owned by the creature

Incidentally, this thread has diverged a lot from the original question, which was about normalization! I found this quote on normalization in wikipedia, which more or less sums it up:

every non-key attribute "must provide a fact about the key, the whole key, and nothing but the key."

(http://en.wikipedia.org/wiki/Third_normal_form)

Just an afterthought: normalization isn't the be-all and end-all of database design. The version you've come up with, whether normalized or not, would work, and if the distinction between users and creatures is important to the concept of the game, then by all means keep it that way, especially if it makes it easier for your own thoughts and creativity to program the game in a more intuitive fashion. What I'm trying to say is, don't be afraid to break normalization if it makes your life easier - database resources ultimately are far far cheaper than human brain-power!

[Misticism]

Hehe it certainly does seem we have different ideas for the game concept. But regardless, your ideas are winning me over. I have to think about it and understand it a bit more.

Clarification: I am creating a game where the user is the player, and the creatures are all minions of the user (or player) A single player can own multiple creatures, but creatures can never own players. Also, Users/Players will be able to have lots of inventory. He equips his stock to creatures. Players can own anything (currently) but creatures can only equip certain things. So example might be two types of health pots. Strong ones that can only be used between battles (only owned by players can't be equipped to creatures), and slightly weaker ones that can be used in combat (and therefore would need to be able to be equipped to creatures)

I hadn't necessarily thought of how I want to manage NPCs though. This really is a evolving project and I really wanted to start small and grow it as a way to learn PHP and MySQL. I just felt like the database design is an important part to try and really understand up front. I'm getting there though.

Thanks!

[Simon Tite]

Actually, I found this thread quite interesting: although I've been programming for many, many years, I've never tried programming a game before!

The database part *is* very important, because once you've got that more or less right, well the door is then open to many other languages, apart from PHP. And once you know MySQL, all the other variants (Oracle, Posgres, etc) will be no problem at all.

Good luck with your game!

[Misticism]

Heh, perhaps you should try it sometime :) It's been a fun journey so far and I haven't even created my database!

Thanks for your help!