Jump to content

  • Log In with Google      Sign In   
  • Create Account


mysql database


Old topic!
Guest, the last post of this topic is over 60 days old and at this point you may not reply in this topic. If you wish to continue this conversation start a new topic.

  • You cannot reply to this topic
17 replies to this topic

#1 Xeinnex   Members   -  Reputation: 130

Like
0Likes
Like

Posted 26 December 2012 - 07:28 AM

well im working on a browser based game right now and i came to the part when i need to structure what will be my database, shortly after i started thinking about it i realized i dont have enough experience managing databases at all, so i got a couple questions about it, hopefully one of you can help me out here:

 

so in the game i want each user to be able to unlock certain items, so i need to keep track of that lock/unlock thing for each item, what i dont know, is how that would work in the database, should i have a user database separate from the item database? or should i use 2 tables inside the same database? how would it work? 

 

actually being more general, how would the whole database work when it comes to users? what they can unlock, their current status, sessions?, everything related to the database is useful for me right now.

 

also these items im planning to let the players unlock, can be  more than one, so they would have quantity, so how do i connect an item-table to each user?

 

im not sure im being clear enough, im not even sure i understand what i need to be asking here, what i do know is that im confused and any help might be just what i need to get going.



Sponsor:

#2 Sollum   Members   -  Reputation: 674

Like
1Likes
Like

Posted 26 December 2012 - 11:01 AM

Well with items it could be like this.

 

Table CHARACTER { ID, USER_ID, RANDOM_FIELD_A, ..., RANDOM_FIELD_N }

Table ITEM { ID, RANDOM_FIELD_A, ..., RANDOM_FIELD_N }

 

 

Table CHARACTER_ITEMS { ID, CID, IID, RANDOM_FIELD_A, ..., RANDOM_FIELD_N } (where CID and IID are foreing keys from CHARACTER and ITEM)

 

If you're going to use MySQL, i'd advise you to read some literature on RDBM.

 

Here's a fine example

 

320px-ER_Diagram_MMORPG.png



#3 Xeinnex   Members   -  Reputation: 130

Like
0Likes
Like

Posted 26 December 2012 - 03:38 PM

i have seen those extra tables before, like 1. characters, 2. items, 3. character-items.

 

but i dont quite get it, what if i want several items for 1 single character, do i need to get a lot of those #3s?



#4 Xeinnex   Members   -  Reputation: 130

Like
0Likes
Like

Posted 27 December 2012 - 12:53 AM

i read there was a way using explode, where i would put all the data in a text element on a table, but i dont really liek it, i want to know how to link a user to his own item table



#5 Geraint   Members   -  Reputation: 178

Like
1Likes
Like

Posted 27 December 2012 - 02:29 AM

I think you're looking for something along the lines of:

 

Character <- Character-Item -> Item

 

Each character has a unique ID.

Each Item has a unique ID.

 

Each time a character "obtains" an item you add a record to the Character-Item table which contains both the unique ID of the character that "obtained" the item and the unique ID of the item that was "obtained".

 

Assuming you used the design that Sollum suggested (which is a very effective one) such that your tables are defined as:

 

 

Table CHARACTER { ID, USER_ID, RANDOM_FIELD_A, ..., RANDOM_FIELD_N }

Table ITEM { ID, RANDOM_FIELD_A, ..., RANDOM_FIELD_N }

 

 

Table CHARACTER_ITEMS { ID, CID, IID, RANDOM_FIELD_A, ..., RANDOM_FIELD_N } (where CID and IID are foreing keys from CHARACTER and ITEM)

 

 

 

That way if you wanted to get what items the character has you would perform a SELECT query such as:

 

SELECT IID FROM CHARACTER_ITEMS
WHERE CID LIKE '0'

 

Which will give you a list of unique IDs for items that a character with the unique ID of 0.

 

I recommend reading up on RDBM as Sollum suggested if this isn't clear to you:

  • Fairly decent article explaining database relationships on TechRepublic: Relational Databases
  • A good article from nettuts+ explaining database relationships (slightly more beginner friendly): Database Relationships

 

If you still need help I wouldn't mind lending a hand if I have some free time so feel free to pop a PM my way.



#6 Xeinnex   Members   -  Reputation: 130

Like
0Likes
Like

Posted 27 December 2012 - 05:11 AM

is there a way for it not to store the items one by one? but have like a table of items for each character?, the way you are suggesting, everytime a user gets an item, the character_items adds a new row to store the item, also what if the character gets an item he already has? how would it increment?



#7 Xeinnex   Members   -  Reputation: 130

Like
0Likes
Like

Posted 27 December 2012 - 05:21 AM

i have another question that i will also ask here because i think its related.

 

 

lets say i want something like an rpg, i have my user table with my user called 001, that user 001 has one item from the item table with the id of 123.

 

 

so 001 has one 123, somehow i have that on my database.

 

now lets say 123 is a potion that restores 100 life points to my user, how would i store this effect on the database? like how would it appear in the database that the item must restore 100 life points to the user?



#8 Sollum   Members   -  Reputation: 674

Like
1Likes
Like

Posted 27 December 2012 - 10:05 AM

EFFECT (ID, TYPE, VALUE1, VALUE2, VALUE3)

 

for example

 

123, TYPE.RESTORE_HEALTH, 15 (over 15 seconds), PERCENTAGE, 15%

 

when you pool out effect, you simply check its TYPE, and then do needed stuff with VALUES.

 

 

Well, that's at least what i usually do.



#9 Geraint   Members   -  Reputation: 178

Like
1Likes
Like

Posted 27 December 2012 - 10:38 AM

You can easily have multiple amounts of the same item.

CHARACTER_ITEMS { ID, CID, IID, Amount }

 

Then you merely modify the Amount value to increase or decrease the number of items a character has.

 

The alternative is you store the list of items as comma separated values in the Character Table. However this is highly inefficient and will cause repetition of data, which is what relational databases are designed to eliminate.

 

I don't really see why you're against using a joining table, could you explain your reasons?



#10 Xeinnex   Members   -  Reputation: 130

Like
0Likes
Like

Posted 27 December 2012 - 02:01 PM

im not quite sure, it just feels more efficient if u could virtually link a user with his own item table, instead of checking one by one every item the user has gotten on a database lots of users are going to use, lets say i have 1000 users and 1000 items, whenever i wanna see items for user 123, i would have to loop through the entire character_item table, to check for his CID, i am fairly new to the database subject but that just feels unefficient, i guess im just thinking like object oriented.



#11 Xeinnex   Members   -  Reputation: 130

Like
0Likes
Like

Posted 27 December 2012 - 02:04 PM

EFFECT (ID, TYPE, VALUE1, VALUE2, VALUE3)

 

for example

 

123, TYPE.RESTORE_HEALTH, 15 (over 15 seconds), PERCENTAGE, 15%

 

when you pool out effect, you simply check its TYPE, and then do needed stuff with VALUES.

 

 

Well, that's at least what i usually do.

 

oh i see now, so if i have multiple possible values, i dont have to have them one by one on the table, like poison and stuff, i just have them as values and use those values according to the type, very interesting.



#12 Xeinnex   Members   -  Reputation: 130

Like
0Likes
Like

Posted 27 December 2012 - 02:50 PM

just a quick example of what i need to be clear:

 

lets say i have these tables:

 

users:

pk iduser

name

 

 

roles:

pk idroles

role_name

 

priviledges:

pk idpriv

priv_name

 

alright so i have an user: iduser=0, name=test

 

i have roles: 0=admin, 1=moderator, 2=member, 3=guest

 

so i would have another table to state a relationship:

 

user_role:

pk iduser_role

fk iduser

fk idroles

 

up until this point its pretty easy, each user will have one role, but then mr. priviledges joins the party.

 

we have these priviledges: 0=view, 1=post, 2=edit, 3=delete.

 

so now we need another relation table and this one is the one i dont quite understand, because using your method, i would have to do the following:

 

role_priviledge:

pk role_priviledge

fk idrole

fk idpriv

 

so if i want to give admins full rights, i would have to add 4 elements to that table, like
 

 

 

pk role_priviledge=0

fk idrole=0

fk idpriv=0

then one for fk idpriv=1, then one for 2 and one for 3.

 

this is the way that role_priviledge table would work, so if possible, i'd like it to work on a more efficient way, like admin has rights 0,1,2,3, all in one element.

 

the reason why i feel this is a need, is because if i had 100 roles, and 1000 priviledges, the role_priviledge table would be a hell to query and to understand.



#13 Geraint   Members   -  Reputation: 178

Like
1Likes
Like

Posted 27 December 2012 - 06:23 PM

I'm not quite sure, it just feels more efficient if u could virtually link a user with his own item table, instead of checking one by one every item the user has gotten on a database lots of users are going to use, lets say i have 1000 users and 1000 items, whenever i wanna see items for user 123, i would have to loop through the entire character_item table, to check for his CID, i am fairly new to the database subject but that just feels unefficient, i guess i'm just thinking like object oriented.

 

That's why you use a MySQL database. I'm not sure why you're looping through the entire characther_item database anyway?

 

Joining tables are actually the how relational databases are implemented. Again I recommend reading up on the subject. They are the efficient method of associating tables together when using a database.

 

Example Scenario

 

  1. We have a Character.
  2. We know his unique ID.
  3. We perform a SELECT query.

At no point do you have to iterate through the character_item table?



#14 Xeinnex   Members   -  Reputation: 130

Like
0Likes
Like

Posted 27 December 2012 - 11:06 PM

i thought SELECTs were like looping through the entire database, well all your help should help me be on the way, now i suddendly dont feel stucked, and i can go on and keep working on my project, thanks a lot



#15 aburningflame   Members   -  Reputation: 113

Like
1Likes
Like

Posted 28 December 2012 - 12:00 AM

They have given you the correct answers. Based on your posts it seems you do not know much about Relational databases.

 

Character (Table)

character_id
name

 

item (Table)

item_id

name

 

character_item (Table)

character_id

item_id

amount

 

 

Example Entries:

 

Character  

1, Juicebox

2, Rupert

 

Item

1, HP Potion

2, MP Potion

3, XP Potion

Character_Item

1,1,5   - Row 0 (Character 1 has HP Potion x5)
1,2,10 - Row 1 (Character 1 has MP Potion x10)
2,3,15 - Row 2 (Character 2 has XP Potion x15)
2,1,3   - Row 3 (Character 2 has HP Potion x3)

 

Executing this SQL:

SELECT * From Character                                                    will bring back information for all characters (rows 0 and 1 in Character table)

SELECT * FROM Character WHERE character_id=1            will bring back row 0 in Character Table "Juicebox"

SELECT * FROM Item                                                            will bring back all rows in t he item table

SELECT * FROM Item WHERE name='HP Potion'                  will bring back 1 row for every item named HP Potion

SELECT * FROM Item WHERE item_id=1                              will bring back 1 row for item_id 1 (Row 0 - HP Potion)

SELECT * FROM Character_Item                                          will bring back all rows

SELECT * FROM Character_Item WHERE character_id=1    will only bring back row 0 and 1.
SELECT * FROM Character_Item WHERE character_id=2     will only bring back row 2 and 3.

SELECT * FROM Character_Item WHERE character_id=2 AND item_id=3   will only bring back row 2 (you can run something like this to see if a character has a certain item ID)

 

 


Here is some basic SQL:

http://www.roseindia.net/mysql/mysql5/Mysql-select-statement.shtmlhttp://www.roseindia.net/mysql/mysql5/Mysql-select-statement.shtml

 

 

Note: SQL Server and MySQL both use SQL - there may be slight differences in syntax / keywords, however, the concepts are the same.

I advise you to pickup mysql and mysql workbench and work on creating tables and writing queries such as:

 

SELECT queries

INSERT queries (http://www.techonthenet.com/sql/insert.php)

DELETE queries


Edited by aburningflame, 28 December 2012 - 12:05 AM.


#16 aburningflame   Members   -  Reputation: 113

Like
1Likes
Like

Posted 28 December 2012 - 12:10 AM

i thought SELECTs were like looping through the entire database, well all your help should help me be on the way, now i suddendly dont feel stucked, and i can go on and keep working on my project, thanks a lot

Selects return a "ResultSet" Which is 0 or more rows based on your select statement.

You can add a "WHERE clause" to your select to limit t he data being returned.

 

SELECT * FROM Character WHERE active=1   <<<Gets all Active Characters
Lets say you had 50 characters, and 10 of these were "banned". The above statement would return 40 rows! Only the # of rows matching the criteria.

With this resultset you can do what you will.


Loop through it in your game, check values, etc etc etc

 

 


Steps I believe you should take:

Download MySQL and MySQL workbench

Practice Creating tables and then performing sql commands on those tables (SELECT, SELECT with where clauses, SELECT with inner joins, DELETES, INSERTS) etc etc.

 

Once  you are COMFORTABLE with the above. Then you can start looking into how to execute sql and loop through resultsets in whatever language you are using.

Here is a small PSEUDOCODE example

 

ConnectionObject Creation

Open Connection

Execute Statement

(Optional)Loop Through Reader & Process Rows

Close Reader/Connection



#17 aburningflame   Members   -  Reputation: 113

Like
1Likes
Like

Posted 28 December 2012 - 12:15 AM

im not quite sure, it just feels more efficient if u could virtually link a user with his own item table, instead of checking one by one every item the user has gotten on a database lots of users are going to use, lets say i have 1000 users and 1000 items, whenever i wanna see items for user 123, i would have to loop through the entire character_item table, to check for his CID, i am fairly new to the database subject but that just feels unefficient, i guess im just thinking like object oriented.

SELECT * FROM character_item WHERE character_id=123

 

This is very efficient. Database engines are blazing fast.

 

 

 

SELECT * FROM character

[code to programatically loop and check for user 123]

^INEFFICIENT

 

 

SELECT * FROM character WHERE user=123 (this will only return x items. where x is the # of items user 123 has)

[code to programatically loop and assign the returned resultset to user 123's inventory]

^EFFICIENT

 

http://www.w3schools.com/sql/sql_where.asp   Keep hitting next chapter.

 

Good luck!


Edited by aburningflame, 28 December 2012 - 12:19 AM.


#18 Sollum   Members   -  Reputation: 674

Like
1Likes
Like

Posted 28 December 2012 - 01:33 PM

i thought SELECTs were like looping through the entire database, well all your help should help me be on the way, now i suddendly dont feel stucked, and i can go on and keep working on my project, thanks a lot

I would advice befriending joins. Main workhorse of RDBM.

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------

--For example, select all character names that have healing potions, by item id

SELECT CH.NAME FROM 
CHARACTERS CH
INNER JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CID
WHERE CHIT.IID = 5

--by name

SELECT CH.NAME FROM 
CHARACTERS CH
INNER JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CID
INNER JOIN ITEMS IT ON CHIT.IID = IT.ID
WHERE IT.ITEM_TITLE = 'Healing Potion'

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------

--Select all character names that have no items overall
SELECT CH.NAME FROM 
CHARACTERS CH
LEFT JOIN CHARACTER_ITEMS CHIT ON CH.ID = CHIT.CID
WHERE CHIT.ID IS NULL

I wont explain difference between joins, so you would go and study them on your own, but have in mind, you shouldn't abuse * to much, select only whats needed and not everything. Don't be afraid to use indexing and avoid doing searches by text as much as possible on classified fields. If you are looking for a Sword of Uberness don't go for "WHERE X.NAME = 'Sword of Uberness'", its slow, make sure application passes classifier ID, because searching for 1337 on indexed not null int field is far faster that doing varchar search.


Edited by Sollum, 28 December 2012 - 01:36 PM.





Old topic!
Guest, the last post of this topic is over 60 days old and at this point you may not reply in this topic. If you wish to continue this conversation start a new topic.



PARTNERS