[SOLVED] MySQL Structure advice

[Derleek]

I'm a bit new to MySQL and PHP... so i think this should be a simple question/answer.

 

I'm creating a MySQL database that needs to support MANY different users and save all of their information (for a game).

 

I have a decent amount of experience using C++, i'm just now venturing into the php realm.  I can conceptualize an array to fit the data i need quite easily, i'm just not sure how MySQL handles what would be a 'multi-dimmensional array' in C++

 

I'm wondering if i should create an individual table for every user or just use one table to store all users and all of their information.

 

i picture the data base i need as follows

 

*Username*(main table)

 

Sub sections:

Password

realName

game choices [needs to store 10 different choices]

raceNumber

 

Does MySQL support multi-dimmensional arrays (which i think i would need for game choices) or is there a good to tie information together.

 

-thanks for reading my programming dilemma's 

[smartin1017]

For the db question, I would use 1 table for all users. I would include a field for email becuase you can guarentee that that will always be original, you can make it your primary field and if needed in the future you can create another table to store info for the same users and link them using email.

 

As for the array, I am not quite sure what you mean by multidimentional, you can definately use arrays but what are you trying to accomplish this will better help me answer the question.

[fenway]

Email as PK? No.

[Derleek]

so you can store array's in a database?

 

i haven't had much time to play around with MySQL much, but its finals week so i can probably figure the best we to structure the database i need then.

 

thanks

[fenway]

so you can store array's in a database?

No you can't... or rather, you shouldn't.

[crtreedude]

Since a table/row/column definition is a form of an array - just persistent, there is no reason to store an array.

 

Perhaps the best idea is to look at Entity Relationship Diagrams , do a little search on them and I am sure you will see how to lay this out.

 

I am thinking the best would be to have 2 or 3 separate tables joined on keys.

 

USER, I would use something like USER_ID as the primary.

GAME - to discribe each game, might only be GAME_ID and DESCRIPTION

USER_GAME, intersect between USER & GAME

    USER_ID

    GAME_ID

    .... other fields

 

I hope this gives you an idea

 

[GingerRobot]

so you can store array's in a database?

 

i haven't had much time to play around with MySQL much, but its finals week so i can probably figure the best we to structure the database i need then.

 

thanks

 

While you can store an array in a database by serializing the data, 99% of the time that you could consider using an array you want another table.

[crtreedude]

One thing to realize is often you might be asking the wrong question. For example, you assume that you should be using an array when perhaps it is the wrong thing. As the old saying goes, when all you have is a hammer, everything looks like a nail.

 

It is very important not to just try to do the same ole things with a new technology (new to you) but to spend some time understanding how it works.

 

It will save much headache later.

[Derleek]

very true treedude... i wasn't quite sure how to format the question i wanted answered.

 

I had a conversation with my uncle and he basically suggested the same thing as you.

 

to have a user table, and a table with the game/info about the game as well as a table that interacts between the two.  I just couldn't wrap my brain around how awesome MySQL was hahaha.

 

thanks,

 

you guys rock