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.

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

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?

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

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.

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?

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?

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.

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?

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.