Sign in to follow this  
Drethon

Using databases

Recommended Posts

I'm looking at how to use databases for a MMORPG style game but am having some difficulty with how to format the databases. The following is what I had in mind for the structure of the game objects using C++ inheritance:
                        Game Element
                             |
                      ______/ \______
                     /     /   \     \ 
                   Area  Room  Exit  Item
                                       /
                                Character
Basically everything is a Game Elements with a name and description (and what ever else might come to be common). Also everything you can pick up or perform certian actions on are items, a character being one since I wanted to be able to pick up and throw characters :) I was considering trying to port this to a database with every class becoming a table but I'm having issues with how to properly represent inheritance in databases where multiple tables can inherit from a single one causing strange behavior. Any suggestions or am I going the wrong direction? Thanks.

Share this post


Link to post
Share on other sites
When translating an OO design to a relational model there are several ways of mapping an inheritance tree. Suppose B and C both inherit from A:

Mapings
1. Three tables fully denormalized (a lot of overlap)
2. Three tables: Table A containing a foreign key to a B or a C (complex queries)
3. Two tables (A is abstract, B and C overlap)
4. One table that contains all occurences and therefore has a lot of empty fields (ugly)

Cheers

Share this post


Link to post
Share on other sites
You don't want to model byte for byte the data structures you have in your game, to your database. Only choose what you need and in a lot of cases you won't even have to have relations and multiple tables where in code you might have inheritence or composition...

For example, just have a Character table with things like name, position, etc... theres no need to do anything weird here like you have to in your code. If you want to share common things like name and description, just have a seperate table called "game element", with "name", "description", and "game_element_id" as members. Give your Character table a "game_element_id" member as a foreign key to link the 2 togeather.

Share this post


Link to post
Share on other sites
Graveyard's suggestion what what I have been considering but my only concern with this is the name is going to be the most used item in the game (text based). When ever you do anything with an item, use access it using its name (if there are multiples you use second, third, etc).

Because of this, I've been looking at most items being considered a Game Element until more specific things are needed. When more specific things are needed, the Game Element entry will need to know what more specific item it is, requiring both a "my specific" table and ID field. I haven't heard of having a reference to another table in a field before and am not sure if this is a good idea or not...

Share this post


Link to post
Share on other sites
Quote:
Original post by Drethon
Graveyard's suggestion what what I have been considering but my only concern with this is the name is going to be the most used item in the game (text based). When ever you do anything with an item, use access it using its name (if there are multiples you use second, third, etc).

Because of this, I've been looking at most items being considered a Game Element until more specific things are needed. When more specific things are needed, the Game Element entry will need to know what more specific item it is, requiring both a "my specific" table and ID field. I haven't heard of having a reference to another table in a field before and am not sure if this is a good idea or not...


The last line of your post sounds to me like you don't understand what foreign keys and relations are... You should use these as much as possible and everywhere it makes sence.

You are correct about the "my specific" table and ID field. Have the specific table have a game_element_id field which is a foreign key pointing to the game element table.... then you can easily get the more specific info from by just doing "select * from some_specific_thing where game_element_id = " + game_element_id. You can easily tell if an item or whatever has more specificness qualities to it by just checking each specific table with the given game element ID.

You should use relations as much as possilble for lots of reasons... For example if your character can have a status such as sick or dead, then instead of making a "status" varchar field in the character table, make a "status_id" field which is a FK pointing to a status table, which has entries in it such as "sick" and "dead"... This keeps things nice and airtight.... kind of like using an enum instead of a const int...

Share this post


Link to post
Share on other sites
I know more or less about relationships but nothing about foreign keys. I'm using this project to lear databases among other things so this helps.

Thanks.

Share this post


Link to post
Share on other sites
Quote:
Original post by graveyard filla
You should use relations as much as possilble for lots of reasons...


I agree, except for the "as much as possible" part, I would word it a little differently.

Be careful, a common mistake is to over normalize the database, which can cause problems when tables get large and have to be joined to 5 other tables which are also large (contain many records). That's a lot of table scans to find one record of data. Indexes help with this, but if you index 5 fields in a table you probably are using more storage space than you saved by not storing redundant data, and it also kills performance when adding or deleting records, or editing fields with indexes in that table. If you are concerned about performance a healthy mix between normalization and data redundancy can go a long way. Sometimes storing actual info rather than ID's is the best way to maintain historic integrity, but for a game I don't see why that would really come into play.

Just my 2 cents

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this