PHP: Main
PHP: Database Structuring 101
Introduction
As of late, I've been helping a number of people with their PHP inquiries. A lot of those people had MySQL questions.
As I talked with these people, it came very clear to me that most people don't know how to properly structure a database. They don't know about relational data, unique ids, things like that.
So, if you're new to MySQL, you should read this to learn how to property structure your database.
Unique IDs, what are the used for, why should I use them?
Unique IDs, know as the primary key in your database table, are one of the most important concepts you need to understand when using databases.
What a unique ID is, is a number that is unique for each row in the table of a database. So each row has a different number, and usually these numbers go in sequential order. MySQL has a feature called "auto_increment". What auto_increment does is cause the primary key, your unique IDs to increment one value every time a row is inserted into that table.
Why is it so important to use unique IDs? Well, let's use this example. I'm making a movie database. Two movies can easily have the same name. Let's say I'm not using a unique ID for my primary field, by table would look like this:
------------------------------
|movie_name|movie_description|
------------------------------
|Titanic|Titanic movie from 1953 staring Barbara Stanwyck.|
------------------------------------------
---------------
|Titanic|The gripping tale of the Titanic from 1997 staring Leonardo DiCaprio|
------------------------------------------
---------------
So, we have a problem. What if I want to select the Titanic movie from 1997 and show it's description? There's two movies named Titanic, and I have no way of telling them apart.
Now, how would this have looked if we had a primary, unique ID?
------------------------------
movie_id (auto_increment)(primary)(int)|movie_name|
movie_description|
------------------------------
1|Titanic|Titanic movie from 1953 staring Barbara Stanwyck.|
------------------------------------------
---------------
2|Titanic|The gripping tale of the Titanic from 1997 staring Leonardo DiCaprio|
------------------------------------------
---------------
Now each movie has it's own unique number assigned to it. If I wanted to pull the data on the awesome 1997 Titanic, all I have to do is select movie_name and movie_description WHERE movie_id = 2. It's as simple as that.
So, you may be asking, how do you make an auto increment unique ID primary field? It's simple using PHPMyAdmin.
Simply add a new field with these settings:
Field = any name you want for your unique ID
Type = INT
Extra = auto_increment
Primary = selected
And you're all set!
Relational data, what is it, and how do I use it effeciently?
The way you store data is EXTREMELY important. Storing data in an impractical way can cause major problems with large systems.
First, I want to go over relational data. Let's say you have a users table in your database. That table has fields like username, password, location, email address, stuff like that. It should also have a unique auto increment ID like: user_id.
OK, so let's say we have our users table set up like this:
------------------------------------------
----------
user_id|username|password|location|email|
------------------------------------------
----------
3|DFox2|loldonthackme|Syosset|david@phpmai
n.com|
------------------------------------------
----------
OK, so that's my user record in the database. Let's say I'm making a portal, and users are going to be submitted tons of Flash content.
When a user submits a piece of content, I need to save that info in another table, called flashes. Here's the important part: IN flashes, I don't want to store the username of the user who submitted it. I want to store their USER ID as the creator, NOT username. Why? What if a user has submitted 100 flashes, and wants to change their username? I'm I going to change it in 100 rows? No, all I have to do is change the username in the USERS table, and it will take effect everywhere else as I have flash submitions tied to the user_id and NOT the username. User id will NEVER change.
So, my flashes table would look like this after I submit a flash:
------------------------------------------
----------
flash_id|user_id|flash_name|views|num_vote
s|
------------------------------------------
----------
1|3|Hop To The Top|324553|2100|
------------------------------------------
----------
It's as simple as that. That is relational data. The user_id within the flashes table relates to the users table as you can do a lookup of a user in the users table just by using the user_id in the flashes table. This is a very important concept, so please make sure you understand it.
Structuring your database properly
A few days ago, someone comes to me with a PHP question. They were making a feature on their site where people could make a favorites list in their account of their favorite flashes.
This is how they had their database structured:
------------------------------------------
----------
user_id|user_name|email|favorite_1|favorit
e_2|favorite_3|favorite_4|favorite_5|
------------------------------------------
----------
IT MADE ME WANT TO PICK MY DOG OFF THE FLOOR AND THROW HIM OUT THE WINDOW. Augh, it pissed me off. What if you want to have the user be able to select unlimited favorites???
I'm going to say this once. YOU DO NOT PUT REPEATING DATA IN COLUMS, YOU PUT IT IN ROWS. Soooo many people make this error. The proper way to do this is:
- Have a table called favorites
- Have it uses a relational field called user_id to tie it to a specific user
- Each favorite gets one row.
It would look like this with some data:
------------------------------------------
----------
favorite_id (auto_increment)|user_id|favorite_movie_id
------------------------------------------
----------
1|3|6
2|3|300
3|3|246
4|3|69
5|6|27
6|6|32
Then, if I want to select all of users 3 favorites, all I have to do is select from favorites WHERE user_id = 3
So, that's basically it. I hoped this helped you learn how to structure your databases.
Be sure to check out PHP: Main to learn how to actually use MySQL with PHP.