----------------------------------------------------------------------------------
Session 2: Database layout
Second to actually programming a website for me in terms of not being easy is laying out what the database will look like. You need to decide what each field's type will be and what type will be the best for a given variable. Not to mention what you're going to put in the database, whether or not some piece of data is best left to do in the program, and in the case of a Friends feature, how you're going to store friend lists!
Like my programming, I like to get the most out of the least effort. This means I use a relational database type setup. I'm sure it's not a true relational database that I'm using, in fact I think this is a "keyed index" database, but the idea is that I have tables for user log in data, friends, profile data, photo's, etc. While this adds to the complexity of the query statement, it makes everything ultimately smaller and makes changes in one place available in all others. In other words, unless we need to start planning on more servers to handle the load of a bazillion users, no waiting 24 hours for photos to change on friend lists.
Anyway, here are some example tables (in MS Excel format). Notice we're not storing anything related to passwords, just some information about who this user is. Why? Because why open up the possibility for others to get the password hashes of users? Well that's mostly because I like to use the '*' in my queries and then push all that returned data into the template engine.
General profile info
"userid","fname","lname","display","gender","age","city","state","country","aboutme","headshot"
Photos. 'bin' is a longblob to store the photo.
"photoid","userid","bin","imgtype","filename"
Profile settings:
"userid","urlname","usefake","friendsonlypm","friendsonlyvp","friendsonlyc","moresettings","etc."
You may ask yourself why have general profile data and profile settings as two different tables? Because in the future I'm thinking I may add profile setting profiles! Much like what Windows has for hardware. Same machine, same users, just different driver setups (as far as I understand). That and when you get insanely huge tables it's hard for me to keep track of what's where.
Friends:
"userid","friendid"
This is the actual table from my myspace clone database. These are the only two fields and it is the part of this project I'm most proud about.
Lets say User A and User B are friends. Generally you'd have one entries for each stating they're both friends. Why do that? I guess one reason would be easy testing to see if User B has approved to User A to being their friend. But I digress. Why not just make a query that checks to see if User A is either in the userid or friendid cell? One row, and we show a relationship between two users. Brilliant.
Anyway, what ties almost every table together is 'userid'.
So, to sum it up. Plan out your database before you start programming. That's not to say that once you start the programming that you cannot make changes, hardly. You'll be making changes through out the process because either you'll find the database layout won't work the way you thought, or you'll think of a better way to do things.
What have you learned from me today? Hopefully that you need to put a lot of thought into how your database is going to be structured so that not only is it easy to get data out and in, but also so that it is easily expanded for new features on your site. Or you could have learned nothing and I'm just rambling.
But anyway, next time we'll have a look at cookies, session objects and keeping real passwords out of the wrong hands.