Confused on how to approach database

[RottenBananas]

Hello,

I hope this is in the right section.

 

So im trying to make myspace from scratch on my localhost to learn php. I dont know what to do with the database(im new to mysql and php).

 

So far I have one table that holds everything...username, email, name blah blah

Here are my questions:

 

1. Assuming my site was real and I had millions of users what are the pros/cons of having all that info in one table? I have a weird feeling having it all in one table is dumb but i dont know any better.

 

2. If i make separate tables how can I access the same row from diff tables. For example I have users table and files table for their pics. users has an id, how do i know that this id corresponds to these pics in the files table?

 

3.I want to have it so they can upload music. What field could that be? Do i need a separate field for every song they upload? or a new table for every user?

 

Just confused on the database stuff I've never worked with them before

 

Your help is much appreciated

Thanks!

[ignace]

1. con: maintenance try normalizing (http://en.wikipedia.org/wiki/Database_normalization) it or post it, we could do it for you

2. join (http://dev.mysql.com/doc/refman/5.1/en/join.html)

3. a new row for every song coupled to the uploader

[RottenBananas]

Thanks for the reply

 

Can you be more specific on 3? A new row for song per user? so a new table for each user with their songs in it?

 

Since im copying myspace think of their database, how should i do it?

[ignace]

table: users (id, firstname, lastname, ..)

table: songs (id, user_id, name, ..)

 

// get user id

$userid = $_SESSION['id'];

 

mysql_query("insert into songs values (null, '$userid', '.. // preferably you would be using prepared query strings instead of this

[RottenBananas]

and how would i retrieve the the songs a certain user has?

 

EDIT: nvm figured it out

[RottenBananas]

OK i wanna make sure i have this right before actually making changes to the db.

 

Users table : id, name, username, password, email

Files table : id, song_id, name, type, size, content(the actual file)

 

To get user X's files i would select * from files where id=$_SESSION['id'];

 

Now do i need song_id? If its the primary key and auto increments itll span across different users.

...sorry im still confused ???