Assuming I want to have a web application that requires storing user information, images, etc as well as storing status updates or posts/comments would I want to separate tables? For example if I have a "users" table that contains users information like passwords, emails, and typical social networking info like age, location etc. Would it be a good idea do create a second table("posts") that handles user content such as comments and/or post?
Table one: "users"
Table Two: "posts"
Is this a valid organization? Furthermore if I wanted to keep track of media should I do this in ANOTHER table?
Table Three: "media"
Any help is much appreciated. I'm curious to see if I'm on the right track or just completely lost. I am mostly wondering if I should have many tables or if I should have larger less segregated tables. Also of note thus far I planned on keeping information such as followers(or friends) in the 'users' table but I'm not sure that's a good idea in retrospect. thanks in advance,
Generally speaking to design a database you create a table for each object you will be dealing with. In you example you have Users, Posts, Comments and Media. From that you can flesh out what it is you want to store for each object. Each item you want to store is a field in the table:
[Users]
ID
Username
PasswordHash
Age
Birthdate
Email
JoinDate
LastLogin
[Posts]
ID
UserID
Title
Content
CreateDate
PostedDate
[Comments]
ID
PostID
UserID
Content
[Media]
ID
Title
Description
FileURI
Taking a look above you can see a basic structure for holding the information for each object. By the field names you can even tell the relationships between the objects. That is a post has a UserID so the post was created by that user. the comments have a PostID and a UserID so you can see that a comment was written by a person for a specific post.
Once you have the general fields identified you can look at some other aspects of the design. For example right now the Email field under the Users table means that a user can have one (1) email address, no more. You can solve this one of two ways... add more email fields (EmailA, EmailB, EmailC) this generally works if you know there are specific types of emails you are dealing with, for example EmailWork or EmailHome. This doesn't work if you do not know how many emails in total there will be. To solve this you can pull the emails out into its own table:
[Users]
ID
Username
PasswordHash
Age
Birthdate
JoinDate
LastLogin
[Emails]
ID
UserID
Email
Now you can have any number of emails for a single user. You can do this for just about any database you are trying to design. Take it in small steps and break your bigger objects into smaller ones as needed.
Update
To deal with friends you should think about the relationship you are dealing with. There is one (1) person with many friends. In relation to the tables above its one User to many Users. This can be done with a special table that hold no information other than the relationship you are looking for.
[Friends]
[UserA]
[UserB]
So if the current user's ID is in A his friend's ID is in B and visa-verse. This sets up the friendship so that if you are my friend, then I am your friend. There is no way for me to be your friend without you being mine. If you want to setup the ability for one way friendships you can setup the table like this:
[Friends]
[UserID]
[FriendID]
So If we are both friends with each other there would have to be 2 records, one for my friendship to you and one for your freindship to me.