database for an online game..

kaila

I'm rather new at this MySQL stuff.. but what I am doing is making an online game.

I have a "management" database, that I really only want to use for the users. In that db, I have four tables. They are users, active_users, active_guests, and banned_users.

In the users table, it logs all of the registration information, such as username, email address, and the sort. In my current login system, I do not have a static id for each user. One of my questions is, will this pose a problem in the long run? The login system I used is the one at Evolt.org with the admin features and such.

In this game, I need to store in a database the horses and other animals, buildings, etc. owned by this user. Is there anyway possible that I can create, say, another database named animals, and in that db include tables for horses, cattle, dogs, and cats? I was thinking that you would have to do this by including one of the same fields in both db's/table(s) such as username, and link them in some way using PHP.

I'm not sure, but my reasoning behind this is that a mysql table can get overloaded and become slow or run out of space? I want to prevent this from happening, if it's even possible. Not to mention this way would keep things alot neater for me and the rest of the staff.

I know this will take alot of work, and it needs alot of complicated PHP, and all that stuff. Let me at least try though.

Thanks
Kaila

danielneri

ok so what i have done in similar circumstances is this.

to find out what 'animals' belong to a user, the users database must have a static identification number. i guess you can call that field id.

once thats done you can add a field to your 'animals' table thats called 'user_id' or something like that, though it can't just be id because that would be for identifying the individual animals.

i know im not explaining it super great, maybe someone else can clear it up for you.

so here would be my query in trying to figure out what animals belong to a certain user.

//connect to the db...blah blah
$sql = mysql_query("SELECT * FROM users WHERE id = '$id'");
while($r = mysql_fetch_array($sql))
{
$r2 = mysql_fetch_array("SELECT * FROM animals WHERE user_id = '$id'");
$username = $r['username'];
$animals = $r2['animals'];
}

ok so with some tweaking this script could be kinda useful i guess. i know better ways im just not super understanding what you want to do even though you explained it great, and im just being lazy

kaila

I think I understand. lol And your really not that bad at explaining complicating things when your lazy. lol.

Let me make sure I have this right though. The $sql is getting the id # from the users table, and the $r2 variable is getting the selected animal from the animal table where the user_id field matches up with the ids in the user table. (wow, i'm not sure if anyone can understand what I just said..)

thank you very, very, much. I'm going to go try it while I'm wait ing on my boyfriend to call me back

xPureNLx

You might want to check out w3schools.com

- xPureNLx

danielneri

thats exactly it kaila. what you said is exactly what the script does

LurkinBack

Really if your going for a truely optimized database you should have a table for animals which holds all the animals people can own and then a seperate one for owned animals which has the animal and user id

Rhino

i agree you need to make seperate tables

kaila

Thank you guys all so much

With optimized databases with all the seperate tables like lurkinback said, which table would you be getting from??

nmallare

For what it's worth. I am the Senior Programmer at Jockstocks.com and have database tables with hundreds of thousands of entries. You don't have to worry about "filling" a database table anytime soon.

And if you're really all that worried, check out this article. EDIT: This forum won't let me post links, so goto google and type in "MySQL archive" [no quotes needed] and read up on the first article. It explains how you can store more stuff, more efficiently. However, it isn't much more efficent until you get into tables with rows of 1,000,000 or more. Also, just an FYI, MySQL can store upto 4GB of information. My biggest on JS.com isn't anywhere NEAR this.

Hope this helps some, or at least saves you the time of making too many tables and what not.

Nick

LurkinBack

I worked on databases that went up to 26mb not MySQL ones but SQL Server 2000. Optimization of a database has nothing to do with space more to do with storage and ease of retrieval.

For a database script to look through a few hundred records in a table takes a short time (but the more records there are the longer the time), in an optimized database it would take even less time as it doesn't have to sort out what it needs and what it doesn't from one long table full of information.

When you're dealing with an online database with users being as picky as they are about time spent waiting on things to load the quicker the load the better, therefore the more optimized the database the better.

Like I said before the more records in the table the longer the retrieval and optimizing a database once it has been created is a pain so, with one eye on the future and the thousands of members you are looking to attract, is it not better to start with a fully optimized database rather than hit a problem later on in time when fixing it can be a nightmare if not impossible, at least with out taking your popular site offline for days?

LurkinBack

kaila wrote: »

Thank you guys all so much

With optimized databases with all the seperate tables like lurkinback said, which table would you be getting from??

Ok the table you search is dependant on what info you want to get out. If you have 1 table for users and 1 table for the animals a user owns then if you want to search for info on a user you search the user table for info on a user animals the other one.

Thats the beauty of optimized databases you can target your search by sorting the tables to begin with rather than trying to be clever with SQL statements.

nmallare

LurkinBack wrote: »

I worked on databases that went up to 26mb not MySQL ones but SQL Server 2000. Optimization of a database has nothing to do with space more to do with storage and ease of retrieval.

For a database script to look through a few hundred records in a table takes a short time (but the more records there are the longer the time), in an optimized database it would take even less time as it doesn't have to sort out what it needs and what it doesn't from one long table full of information.

When you're dealing with an online database with users being as picky as they are about time spent waiting on things to load the quicker the load the better, therefore the more optimized the database the better.

Like I said before the more records in the table the longer the retrieval and optimizing a database once it has been created is a pain so, with one eye on the future and the thousands of members you are looking to attract, is it not better to start with a fully optimized database rather than hit a problem later on in time when fixing it can be a nightmare if not impossible, at least with out taking your popular site offline for days?

Well, if you read the article about arhiving, you'd know how it optimizes stuff ... then you could have saved yourself the writing.

Also, I'm not saying mutiple tables are bad. In fact, at JS we've got almost 400 tables. But what I'm saying is that there isn't necessarliy [sp] anything wrong with large tables.

Nick

LurkinBack

It may optimize your archive but it can never fix bad coding practises at a later date. Your 400 tables are all very well and good but I bet by your logic you could bring that right down to just a few using 1st normal form. Why don't you because sometimes if not ALL the time a 3rd normal form database is the best way to go. Not only for storage issues like hosts not allowing tables to go above a certain size but for simple things like less complicated database queries that take only points of seconds to run rather than whole seconds worth which just drives your users potty staring at a blank screen or spinning hourglass.

No there's nothing wrong with large tables but as any decent DBA will tell you a database should hold all relevant data in the fewest amount of optimized tables. In the case above two tables is the best way to go. Where is the sense in storing all of that data in one table includng all of the users information more than once?

nmallare

Agreed, no need to store the same data twice. I was simply making an off-hand comment about large tables.

Nick