A bit of background first shall we? I've been a webmaster for a while now, and a member of this site for a fair few years, although I rarely stop by unfortunately. I do however, find this site to be an amazing resource to all webmasters.

So for the last while I've been playing with PHP, and for the most part I've gotten it to do whatever I've needed it to do. Lately I've been playing around with more Object Oriented based code (something thats still kind of new to me) and to get a better hang of it, I decided to do what any other person learning something new in PHP would do: Design a web game!

Simple game, not really meant for people to play, just for me to practice with, and have a goal towards. The game I'm making is pretty basic, and for the most part, is a re-written version of something I did about a year ago, but now with cleaner and safer code, and based around Objects.

Now we come to my problem. In the past, I really did not give a care about how mnay MySQL queries per page, or normalization, or really anything of that nature. But this time around Im trying to do things at least a LITTLE more correct.

Within that, I have two main questions regarding Database Design, I want to make sure I do proper planning before moving any further.

The first question is regarding the best way to store data in tables, and retrieve it. Ok thats a very broad question, but I'll be more specific.

Ok, so right now, I have the following tables/Data:

Users table:
(columns)id, email, password, dateCreated, lastVisited
Characters table:
(columns)id, name,race,innate
Stats table:
(columns)id, currentHP, currentMP, maxHP, maxMP, STR, DEF, INT, RES, SPD, EXP, GP, LVL

and my queries have been stuff along the lines of the following:

PHP Code:

$query  = "SELECT * FROM characters WHERE id={$id}"; 


and then all of the information is loaded into a $character object (that can then be retrieved with $character->currentHP, $character->LVL, and so on.) (I THINK I'm using objects right...)

Currently the only time the users table is accessed is when logging in. The characters table, and stats table are accessed every time a logged in player loads a page (and their character is then loaded into an object).

My first question becomes:
Is there a more optimized way to do this?

As in, would it be better to use MySQL Join statements (which are also kind of new to me), to query both tables and join the results based on the id (id being the same identifier to link all of the tables)?
Or would it be best to stick all of the values into one HUGE table and load all of the results at once?

Within this question, aside from having results in memory, is there any way to optimize queries? Pull more information out with less, or pull a bit of information out over 3 or 4 of them? To my knowledge less is better, but how much of an impact does it really have?

Ok, and here is the second question that has really stumped me, and is the real reason I've come to the experts for help.
What is the best way to link a table describing objects, to a table listing who has those objects?
(Not objects as in PHP objects, but as in an Item, a Spell, something someone could have)

So, think of an item system in a game, and along those lines. Specifically, I'm trying to create a spell system. I have a list of spells and their attributes, and I am trying find a way to link that, to who has those spells.

When I originally made this game a year or so ago I had it so that every account created, had its own "skills" table and "stats" table created with it. This meant that there was a TON of data repetition, and a lot of unneeded space. Not to mention, that after just 100 users would sign up, thats 200 different tables right there. However, that way did work, and it worked pretty well from the PHP side of things. Each user only ever needed to query two tables, and the only time more was needed, is if the user was attacking or viewing another user, then only 4 queries where needed for every bit of data.

However, this time around everything I have thought of has... well not worked so well. Here is an example of the content, and way I am trying to make it work:



So, as you can see, they all have an id, a name (ignore the blatant Final Fantasy naming ripoff for the moment lol) a base damage, an element, and a space for if that user has it and how many times they have used it (NOTE: This is based off of the old game, where each user had their own table. The current way I am trying would only have the ID, Name, Base, Element and MP cost).

For the life of me however, I can not find an efficient way to link this to a user... the only thing I came up with that worked, was:
having a table with an ID column linked to each characters ID, then each of the columns along the top with the name or ID of a spell. But what this created, is me having to Query to get a list of spells a user has, and then Querying each and every single spell in turn to get their stats. Which turns into between 4 and 24 queries per page.

There has GOT to be a better way to link the Spells a character has, to the attributes of that spell, without a bunch of different tables or a bunch of different queries.

So, although I am not new to PHP, I'm new to trying to do PHP and MySQL the correct way, and I am VERY open to learning and anyone that is willing to teach. This website has an over flowing amount of people that are amazing at what they do, and I hope one of those people puts in the (far to much) time to read this over and help me learn something new.

Finally, I would like to apologies for the HUGE wall of text. I always tend to do this, its the only way I can really get my thoughts out. And thank you for everyone that takes the time to read over this, and thanks even more to anyone that reply's to help out!

There is much to say, but as I'm at work, I won't be able to say it in the next 8 hours.
I'd just say that you really need to learn about the joins in your sql queries, and you will see that it's not that complicated.
You can already take a look there: http://www.webmaster-talk.com/the-da...tml#post942639

First off, I do hope theres a lot to say, as if you have help to offer I'm right here with you, willing to learn. (has to sleep for about 8 hours before needing to work tomorrow, so that works out!)

So I read the topic you linked me to (it really would have helped if that pastebin haddent have been expried lol) and that (in my mind) cleared things up! Then.. upon trying and failing to adapt the code, I went looking for more Join examples... most of them made it look more complicated then needed, till I found this (which I really should have looked for in the first place) http://www.w3schools.com/sql/sql_join_left.asp and was able to get some basic code working.

But... that still doesnt directly answer ether question, of weather its more efficient to do it the join way, or have them all stored in one huge table. Since all of the data is different, its still normalized... Just organizational wise it looks nicer.

Also, after a few more attempts, I'm still very stumped on how I'm going to join up the spells with the characters. But Open to suggestions!

Till then, Trippy I await your return.

Ok, here we go...

Currently the only time the users table is accessed is when logging in. The characters table, and stats table are accessed every time a logged in player loads a page (and their character is then loaded into an object).

My first question becomes:
Is there a more optimized way to do this?

Not much. You could make the code simpler by using 1 query to fetch everything, but it won't be faster.
Well, maybe a couple of micro-seconds, because the sql parser won't be fired 3 times but 1, but it's really minimal.

Given your table description up there, and extending them a little, the sql query to fetch every characters with all stats for a given user would be

table Users: no changes
table Characters: add a new field userId, that will store the id of the user who own that character
table stats: add a new field characterId, that will store the id of the character whose stat is attached

select *
from Users u
    inner join Characters c
        on c.userId=u.id
    inner join Stats s
        on s.characterId=c.id

That way, rather than having 1 set of 3 tables per user, you have 3 tables for every users.
And you can create a link between the user, the characters and the stats

In this query, we fetch all the fields from each tables.
The inner joins would be translated in English by "match the fields from the previous table with the fields of that table using the field x as a filter"
So, the first "inner join" will tell the db that the tables Users and Characters are to be linked together, and that the value of the field Users.id must be the same than Characters.id to have a matching record.

What is the best way to link a table describing objects, to a table listing who has those objects?

For that, you will use a linking table. In your case:
Table Spells:
id
name
cost
effect
radius

Table Characters:
id
name
race
innate
userId

Table CharacterSpells:
spellId
characterId

With this, you have 1 table listing every spells with your existing Characters table.
The new CharacterSpells table will make a link from 1 character to every spells he can use.
You can get a list of them with

select s.*  --gets every fields from the table Spells, via it's alias
from Characters c   --c is the alias for Characters
    inner join CharacterSpells cs   --cs is the alias for CharacterSpells
        on cs.characterId=c.id
    inner join Spells s --s is the alias for Spells
        on s.id=cs.spellId
where c.id={$characterId}

The user buy or acquire a new spell, then you simply add a new line into the CharacterSpells table.
That way, you define the spells only once, and each time simply refer to it via a linking table.

The same to the inventory, or anything you can think of.

Now, a bit more on the query themselves.
Lets take the previous one, the one to get the spells available for a character.
I will explain a bit further the syntax, to be sure you understand it.

An sql query is based at least on 3 predicaments. What do you want, where does it comes from and how to filter it.
What follow the SELECT keyword represent the fields of each tables you want to see in your resultset.
The syntax is simple, it's either a field name, if it is unique, or "tablename.fieldname" in the case where the same field name can be present in several tables you have joined together.
You can set an alias to the table name, and in that case, you can use the syntax "alias.fieldname"

The FROM clause explain to the db where the datas are coming from. This is mostly the tricky part.
If you have just 1 table, then it's simple.
If you need to join several tables together, it's just a tiny bit more complicated.
First, do you have cardinality between your 2 tables. Which means, do, for every row in the first table, you have at least 1 matching row in the second table?
This have an importance, because given the type of join you use, you can hide an incomplete match.

The joins are a big part of the sql mastery, so don't hesitate to test and read tutorials.
I'm not a teacher, I'm not very good at explaining this in a structured way.
You can find a visual explanation there, that I find pretty good.
http://www.codeproject.com/KB/databa...JOINS_orig.jpg


In the case where you know you have at least one row in each table, then you simply use an inner join.
An inner join simply tells the db that each rows from table A that match table B, given the "ON a.field=b.field" condition is true.
Beware that if you don't have a b.id field relating to an a.id field, then you won't see the row from the table a.
If you want to see incomplete matching, then you must use an OUTER JOIN.

An OUTER JOIN is almost like an INNER JOIN, but you can specify a "principal" table.
In the case where the secondary table don't have any row to match a row from the principal table, then the row from the principal table is still outputted, and you will have "NULL" value on each fields of the secondary table. Meaning that there are no values.

Let see...
Given your spell and character structure we saw previously, you want to list each spells, and which characters have them.

Then, as previously, you could do:

select 
    s.*  --gets every fields from the table s:=> Spells
    ,c.name --The character name
from Characters c
    inner join CharacterSpells cs
        on cs.characterId=c.id
    inner join Spells s
        on s.id=cs.spellId

Letting go the WHERE clause would mean that you won't restrict your query to a given character.
But, using an inner join, this query will only output spells that are used by at least 1 character.

To have the whole list, we would need:
1) to base the query on the spells table
2) to have an outer join to characterSpells to fetch

select 
    s.*
    ,c.name
from Spells s
    left outer join CharacterSpells cs  --Left outer join means that the table Spells is the principal table
        on cs.spellId=s.id
    left outer join Characters c    --After an outer join, you must always use outer joins, do not use INNER JOIN after an OUTER JOIN
        on cs.characterId=c.id

With this, I think you have the base to bring your game further.
Don't hesitate to ask for clarifications, I'm sure this post could use some...

Originally Posted by tripy

table Users: no changes
table Characters: add a new field userId, that will store the id of the user who own that character
table stats: add a new field characterId, that will store the id of the character whose stat is attached

Well, the way I currently have the database set up, is that the id field that is on the users table, is the exact same id thats on the id field for the characters and stats tables. Since they are all the same (and I tested this a bit last night, but not much) I was able to basically join them as users.id = characters.id and it seamed to work for that.

Originally Posted by tripy

That way, rather than having 1 set of 3 tables per user, you have 3 tables for every users.
And you can create a link between the user, the characters and the stats

Not sure what you mean there... are you saying that every user created should have their own sets of tables? The way I'm doing it currently, every character has one row to themselves in each 3 different tables. Accross the 3 tables, the id column links all of them, and is The only item thats repeated.

Originally Posted by tripy

For that, you will use a linking table. In your case:
Table Spells:
id
name
cost
effect
radius

Table Characters:
id
name
race
innate
userId

Table CharacterSpells:
spellId
characterId

With this, you have 1 table listing every spells with your existing Characters table.
The new CharacterSpells table will make a link from 1 character to every spells he can use.

...

That way, you define the spells only once, and each time simply refer to it via a linking table.

So... with the proposed CharacterSpells spells table, each row would have a character id, and the id of a spell, but would only have 2 columns? Wouldnt that mean that for each spell that a single user has, he creates a whole new row to himself, meaning that his id gets replicated up to 22 times? Which, lets say something like 1,000 users signed up ( which is never going to happen, but for the sake of example), would that not mean that the one table could end up having 22,000 rows?

I mean, it makes sense, and it would be an easy way to do it, but it seams like a lot of data gets replicated.

Originally Posted by tripy

Now, a bit more on the query themselves

...

select 
    s.*  --gets every fields from the table s:=> Spells
    ,c.name --The character name
from Characters c
    inner join CharacterSpells cs
        on cs.characterId=c.id
    inner join Spells s
        on s.id=cs.spellId

So, questions as far as the syntax goes.... Ok, ao the s.* part, does that just mean select everything that starts with s? Does it have to be done like that? for c.name, c is the alias you give the character table on the next line correct? So you dont have to give it an alas before you refer to it?

Originally Posted by tripy

With this, I think you have the base to bring your game further.
Don't hesitate to ask for clarifications, I'm sure this post could use some...

Indeed, you've provided a LOT of useful information thank you! This is exactly why I come on this site, people that know how to do things, and are willing to share.

Well, the way I currently have the database set up, is that the id field that is on the users table, is the exact same id thats on the id field for the characters and stats tables. Since they are all the same (and I tested this a bit last night, but not much) I was able to basically join them as users.id = characters.id and it seamed to work for that.

Ok, but that means that 1 user can only have 1 character. I thought he would be allowed to create several characters.

Not sure what you mean there... are you saying that every user created should have their own sets of tables?

No, but it's what I thought you had setup reading your first post.

When I originally made this game a year or so ago I had it so that every account created, had its own "skills" table and "stats" table created with it.

Wouldnt that mean that for each spell that a single user has, he creates a whole new row to himself, meaning that his id gets replicated up to 22 times? Which, lets say something like 1,000 users signed up ( which is never going to happen, but for the sake of example), would that not mean that the one table could end up having 22,000 rows?

It's exactly that, in the case that those 1000 users have all the 22 spells. And don't worry, relational db's are built with that in mind. Having 600'000 rows in a table like this is nothing exceptional.
Basically, you try to limit the replication at the most. And it's better to have 2 integer fields that might get duplicated than a whole lot of fields.

I mean, it makes sense, and it would be an easy way to do it, but it seams like a lot of data gets replicated.

Well, you have to have this information somewhere...

Ok, ao the s.* part, does that just mean select everything that starts with s?

Nope, it means select every fields from the table with the alias "s", which is "Spells" in that case.

Originally Posted by tripy

Ok, but that means that 1 user can only have 1 character. I thought he would be allowed to create several characters.

Maybe at some point way down the line if I redesign the game again, but right now, each user shall get one.

Originally Posted by tripy

Nope, it means select every fields from the table with the alias "s", which is "Spells" in that case.

Oh wow... thats easy to see now, I have no idea how I didnt understand that!

Thank you very much for you're help. I shall play with these ideas and then come back if I need clarification on anything else.

If anyone else has any comments, suggestions or best practices, please feel free to post them! I am eager to learn! Or even just Database Design comments in general.

Just as an update, I'd like to thank you a lot tripy! I've been doing PHP for a while now and you've completely changed how I look at Database Design!
I created this topic thinking that Query Joins where complicated (turns out it was just the research material sucked at explain them.), and after your explanation, it has demystified them for me!

After realizing how easy it was to have separate tables, and join off of separate identifiers, it made my work flow so much faster. You where right, it really did help me get father!

Thanks!