I am new to PHP/SQL and database design. As a "beginner" (if you want to call it that) project, I am attempting to build a browser based game.

I have been advancing steadily so far. I have implemented a login system, built a DB in phpmyadmin, and was able to use PHP to query each row and display it despite any database changes.

Up until now, however, I haven't hit any walls I couldn't overcome with a good google search phrase.

Heres what I'm trying to do :

Users will sign on and have their own account. For this current stage of the project, I am simply trying to grant the user specific "units" which have varying amounts specific to that user.

Example:
User1 has unit1, unit3, and unit5 with quantities 100, 300, 500 respectively.
User2 has unit2 and unit4 with quantities 200 and 400 respectively

What would the correct way to set up this database?

---

My current thoughts:

tbl_user
-----
id | 1 | 2
units | 1, 3, 5 | 2, 4
other | 'xyz' | 'abx'

tbl_unit
-----
id | 1 | 2 | 3 | 4 | 5
name | Zombie | Vampire | etc3 | etc4 | etc5
other | 'abc' | 'xyz' | 'etc3" | "etc4" | "etc5"

Then in PHP, I explode "unit" data, then use those variables to pull in tbl_unit info.

The problem I have is how I can pull in the "amount" data. I dont know where the correct place to add this would be. it wouldn't fit in the unit table or user table, unless i added it in a way similar to tbl_users.unit ...which would just be a nightmare to update (i imagine).

Im just baffled. It would be insanely helpful in my learning process if someone could do a quick ERD explaining how to do this.

I appreciate the help in advance!

If you have undefined numbers of columns in a table then you should look at growing the table downwards instead of across. What I mean by this is each new feature should be added as a new row in the table rather than adding more columns. For example, let's imagine you eventually want to start searching your data? This will now be more complicated if you have fields called unit1, unit2, unit3, unit4 where you are storing at random pieces of information.

If on the otherhand you start growing the table by adding in rows as follows:

tbl_unit
id -
unitDesc id
unitValue

unitDesc id is an identifier to another table that describes what the field represents. For example 1 - Zombie, 2- Vampire

This design does not limit you to 5 entries so if you need to enhance the application later on for more values this will not require a database change.

Hope this helps?

Thank you for the response!

I have tried to understand what youre saying. But application is proving difficult.

In your example, would it fill up with different instances of multiple users data? I suppose what I am asking is, how does that table relate to the user?

You could add a user_id and get something like this:

tbl_unit_amounts
id | 1 | 2 | 3 | 4 | ...
user_id | 2 | 32 | 49 | 32 | ..
unitdesc | 1 | 2 | 2 | 1 | ...
unitvalue | 20 | 15 | 12 | 30 | ...

Then you could query for user_id and return a list of all their units.

'query for user 32'

id | 2 | 4 | ...
user_id | 32 | 32 | ..
unitdesc | 2 | 1 | ...
unitvalue | 15 | 30 | ...

Then call tbl_unit to bring up values for '2' and '1' to do the math.

Again, as someone who has never worked with databases, the following statement may be ignorantly put, but, this seems messy to me.

Is this simply how things like this are done? Or are there other ways to do it?

What I meant was have the table created as follows:

CREATE TABLE tbl_unit_amounts (
id INT,
user_id INT,
UNITDESC VARCHAR(100),
UNITVALUE VARCHAR(100)
)

INSERT INTO tbl_unit_amounts (user_id, unitdesc, unitvalue) VALUES
(2, 1, 20),
(32, 2, 15),
(49, 2, 12),
(32, 1, 30);

If you want all information for user_id = 32 then you simply run the SQL statement
SELECT * FROM tbl_unit WHERE user_id = 32;

This will return all the rows that you need to process.

Perhaps I am missing you explanation above. If you could give a concrete example I might be able to help you further with this?

to explain it a bit better, I wrote out what we have come up with so far in powerpoint:

heres the pic: http://i44.tinypic.com/2dhbs09.jpg

Is there a simpler way that we might possibly be overlooking? Or is this the most efficient way to do it?

Again, im not too concerned with exactly how I will call the data, just as long as its possible. At this point, im just trying to learn the best methods for designing an efficient database.

Ronan has the right way to do it. Grow the database downward, and select by user id.

Actually I think you are doing what we have been talking about. It is just the way you present the data. You are showing the fields as the first entry in each row and then subsequent columns are showing the actual data contained in each row. We normally show it the other way around i.e. show the field names on top and then show each row of data i..e the field values under the field names.

The model looks good.

Thank you guys for the help. And I apologize for my inability to correctly express my line of thought.

I've only been working on php/sql for less than a week now. Which is why I'm trying very hard to learn the fundamentals. As I said, I am working heavily with tutorials and manuals...but often times its incredibly difficult to find out what piece of code you need when you don't know any of the code. This especially rang true for database design. There doesn't seem to be many tutorials about it outside basic "relational database" explanations.

Anyway, while were on the subject, and I have some very helpful users in the thread...here is the code I am using:

Again, I'm working with very limited knowledge of shortcuts, etc to make sure the code is efficiently written. The code should roughly match up to the things we discussed in the thread. (user_units is tbl_u_amounts)

Any advice, tips, redundancies, things I'm doing that I shouldn't?

One question, the unit_id returned from the first query is then used in the second query. The second query returns a single row. Have you considered executing a single query:

SELECT usr.id, usr.unit_id, usr.user_id, usr.amount, u.name
FROM user_units usr
JOIN units u ON (usr.unit_id = u.id)
WHERE user_id = $_SESSION[user_id]

This will return all the values you need in one query rather than querying twice for the information.

Also it is good practice to free up the memory used for the resultset. This will get done automatically when your process completes but if there is a lot of work being performed then it is better to automatically free this up yourself to keep the amount of memory being used by the process down to a minimum.

mysql_free_result($result);