Database driven PHP web2.0 site

The database (DB) structure can make or break a website. It takes a long time to prepare for everything that needs to go on to your site, but once the DB is sorted out, implementing this is a piece of cake.
This article will explore the importance of a good database structure.

Firstly you will need to list out all of what you would like to appear on your site. It could be you just want a list of articles (ahem), or you may need something more complex.
Either way list all of these out, and then from there we will step forward.
For the purpose of this i am going to design the table structre for a simple social networking site, and break down what Web 2.0 looks like under the hood.

So, like i said above we need to list out all of the things our site will need to do. Below is a simple list of all of the basic functionalities that i want "CodersAdvocates Social Network" (i'm not very imaginitive with names) to do:

• Register your details I will need to allow users to register thier details with me, and also enable them to log in and out of the site.
• Logging In and Out Users need to log in and out, sometimes a user may forget the password to get in, so a reset email will be needed.
• Add friends Users will need to be able to search the other users to see if thier friends are registered so they can network to each other.
• Users privacy Certain users may not want other users viewing all of thier details, or maybe want other users blocked from viewing them alltogether.
• Media Users may want to add photos, or media to thier page, so others can see them.
• Wallpaper Users may want to add thier own wallpaper for the page

Lets identifying all of the information that will need to be stored (simplified). Firstly, user details will need storage, so thats Firstname, Surname, Email, Password, Avatar image, a gallary of photos, thier friends, and thier customisations.

When you look into that in a bit more detail, each user, will have friends that are also users. You only need to store each user once, and then find a way of linking them together.
I'll come to that, but lets design the table for the registered users to store thier info...
tblRegisteredUsers
RegisteredUserID
Firstname
Surname
Email
Password I'm going to ignore the security problems of using plain text

The next thing is each user will need to be able to upload photos.
For this example i will be saving them in the database, so the table for that will be
tblUsersPhoto
UserPhotoID
Photo (BLOB)

All you eager beavers will realise that i have missed something out. How will we know which user uploaded which photo?
Answer is simple, we need to store the users ID number (which is a primary key) in the users photo table.
tblUsersPhoto
UserPhotoID
Photo (BLOB)
RegisteredUserID

The above table structure now allows us to store all of the information on a user, and allows them to save thier photos with us. And based on their ID, we are now able to find out which photos belong to each user.

By now you will hopefully be able to see how to link a user with another user and if not don't worry i'm going to cover that soon enough.