Database Design in Games in Design Philosophy

ID:265702
 
Feb 14 2007, 9:51 am
How would I go about grabbing the information from the database? What would be the most efficient way?

PLAN A: Load all of the information on the world.startup (grab all of the item information from the table?)

PLAN B: Load the information whenever it's needed, ex,
select * from potions
where potion.id=5

( That would load the information bout the potion with the id of 5, the magic potion. )
But how would I transfer this to an object?
And how would I go about saving the items list? :(
#1 Feb 14 2007, 11:35 am
You could make your own database system using Byond savefiles, that's what I've done. I've made a DM app that I can use to edit it without being in game, in a similar way you would with MySQL or whatever. I'm also working on a function that will output it all into a text file, that could then be imported into MySQL if needed (say I needed to port the game to a non byond platform I could keep the database).

Running database queries everytime you need to look up some information would probably be quite CPU intensive if you have a lot checks like this. I would be tempted to load it all into ram at the start.

Excluding player accounts of course, since they will grow very big very fast (lots of people creating players then never coming back). You won't need to load stuff from the accounts file very often anyway.

#2 Feb 14 2007, 6:13 pm
In response to Midmarch (#1)
Midmarch wrote:
Running database queries everytime you need to look up some information would probably be quite CPU intensive if you have a lot checks like this. I would be tempted to load it all into ram at the start.

It really depends on the information. If you need the BYOND game to know something, you should load it up. If, for example, there's database information on a map that nobody is currently in, you could leave that in the database. Also, if you want to do any operations that require a DB, like searching, you'll have to get that data from the database.

Flame Sage wrote:
How would I go about grabbing the information from the database? What would be the most efficient way?

Some stuff should be loaded all at the start, while there's lots that can be left in the DB until later. Just think about what's reasonable, and do that.

Concerning technical particulars, take a look at Dantom.DB, and if that will work for your situation.

But how would I transfer this to an object?

How does Read() for example, work?

And how would I go about saving the items list? :(

You might be able to have a table where every entry has a container_id and an item_id. The container_id is referenced to whatever is containing the stuff. The item_id points to where each item has its info stored.
#3 Feb 15 2007, 6:39 am
Flame Sage wrote:
PLAN B: Load the information whenever it's needed, ex,
select * from potions
where potion.id=5

It's actually wiser to only grab the information you need. Instead of grabbing it all with the wile card.
SELECT cell1, cell3, cell5 FROM table_row WHERE cell1 = 2 LIMIT 1

This way you don't grab needless values and waste time. I also hear tale that the wildcard can occasionally loop twice and waste time and processes. However I'm not to sure on that tale. I only breifly remember something mentioned about it a while ago.

As for when it's best to grab the information. It depends, if the database is located off the hosts hard drive, as in, an external webserver. Then at startup is the best time, because you save server lag. However, if the database is located on the same computer the game is being hosted on. Data transfer would be no different to if you grabbed it from an individual savefile. (You also have to take into account, multiple hosts. What happens if two hosts are playing and a player moves from one server to another. I assume you want the user to be able to pick up where he or she left off no matter what.)

So if it's externally, grab the information at start-up (see id:394116). Otherwise, when needed. This is all dependant on one host. Otherwise, the only way to do it is when needed.
#4 Feb 15 2007, 6:48 am
In response to Tiberath (#3)
So..
Say I want to program this in php, I realize how I would grab the information from the table, but what would be the best way?
Example...
//PLAYER TABLE
#id
*name
description
*level as number(3)//That would allow me three digits, right?
//How would I do contents?  I don't want to store each item the player has in the database, would it be better to make some kind of customized text string that I could read from ? Like...
set contents = '(item1{Rod of Death,charge=5})'


I just don't understand how I can program a game in php, through a database, what would the proper way of doing things like that...
You think there are any guides around?
#5 Feb 15 2007, 7:12 am
I think the best solution varies in different situations. World data should be loaded up all at once on world.New(). This means global variables and the like.

Player information should be loaded up all at once, but only when needed. Don't request the data until a player has logged in and actually requires data to be loaded.

If not what you were looking for, I've misinterpreted.
#6 Feb 15 2007, 7:32 am
In response to Jon88 (#2)
Jon88 wrote:
Midmarch wrote:
Running database queries everytime you need to look up some information would probably be quite CPU intensive if you have a lot checks like this. I would be tempted to load it all into ram at the start.

if your database design is good, and you split up tables accordingly (player table, inventory table (linked to players), item table, evironment table (things on the map like trees, rocks, buildings, etc.), and you create smart primary keys and indexes/indices, then the query calls to the database should be quite fast. db engines like MySQL are fairly well optimized to do things quickly so as long as you are not trying to ask for hundreds of megabytes of query results, you should do just fine doing db operations 'on-the-fly'.
#7 Feb 15 2007, 7:38 am
In response to digitalmouse (#6)
So, what kind of tips would you tell people to do when making a game with php? (A text mud, mind you, very basic)

A player table.
A player's inventory table. (Having item1, item2, item3, item4? Or foreign keys to the actual items?)
NPC table?
Room (each ROOM may have a NPC, item, etc.)
#8 Feb 15 2007, 8:05 am
In response to Flame Sage (#7)
i think for a MUD-type game, you could go with a database like this:

mob table (short for 'moveable objects' which could be players, npcs, monsters, gods, etc. putting everyone in here allows you better control over all of them, and might make it easier to do things like take control of an NPC for better role-playing (for example)).
---
mob_id
mob_name
mob_description
(various mob_stats follow)
mob_state (awake, asleep, controlled by someone else, computer controlled)


item table
---
item_id
item_name
item_descrip
item_stats
.
.
.

inventory table
---
mob_id
item_id
inv_state (damaged, new, etc.), and others

rooms and other tables can follow a similar layout




#9 Feb 15 2007, 9:50 am
In response to digitalmouse (#8)
Right, well I could just do...
sub-table
mob
- Player
- NPC
- Monster
item
- potions
- weapons
- armor
- quest_items

Like that, right?
So is there a way to... like...
See what the table's path is?
Ex.
/item/potion or something like that?
#10 Feb 15 2007, 9:09 pm
In response to Flame Sage (#9)
Flame Sage wrote:
Right, well I could just do...
sub-table
mob
- Player
- NPC
- Monster
item
- potions
- weapons
- armor
- quest_items

Like that, right?
So is there a way to... like...
See what the table's path is?
Ex.
/item/potion or something like that?

Or you could go in-depth.

Mob-Table
- mob id
- mob name
- mob type_id

Mob-Type-Dable
- type id
- type name

Something along those lines and linking via ID numbers.
Then you can do double query calls.
SELECT m.mob_name, m.mob_type_id, t.type_id, t.type_name FROM Mob-Table m, Mob-Type_Table t WHERE t.type_id = m.mob_type_id LIMIT 1

Or something to that effect. Digi would be the better candidate to explain it. I only use SQL in PHP to make awesome systems =D

But if you need help with some querie calls, give me a nudge and I'll see what I can do.

#11 Feb 15 2007, 10:09 pm
In response to Tiberath (#10)
PHP and MYSQL are so fun :D