Computer
« [1] »
I'll discuss each table by explaining the SQL, which I created using the scheme above. For your own scripts you can create a similar scheme and SQL too. Some editors like MySQL Workbench (the one I used) can generate .sql files too, but I would recommend learning SQL because it's more fun to do it yourself. A SQL introduction can be found at W3Schools.
Users Table
01
02
03
04
05
06
07
08
09
10
CREATE TABLE users (
user_id INT(8) NOT NULL AUTO_INCREMENT,
user_name VARCHAR(30) NOT NULL,
user_pass VARCHAR(255) NOT NULL,
user_email VARCHAR(255) NOT NULL,
user_date DATETIME NOT NULL,
user_level INT(8) NOT NULL,
UNIQUE INDEX user_name_unique (user_name),
PRIMARY KEY (user_id)
) TYPE=INNODB;
The CREATE TABLE statement is used to indicate we want to create a new table, of course. The statement is followed by the name of the table and all the columns are listed between the brackets. The names of all the fields are self-explanatory, so we'll only discuss the data types below.
user_id
"A primary key is used to uniquely identify each row in a table."
The type of this field is INT, which means this field holds an integer. The field cannot be empty (NOT NULL) and increments which each record inserted. At the bottom of the table you can see the user_id field is declared as a primary key. A primary key is used to uniquely identify each row in a table. No two distinct rows in a table can have the same value (or combination of values) in all columns. That might be a bit unclear, so here's a little example.
There is a user called John Doe. If another users registers with the same name, there's a problem, because: which user is which? You can't tell and the database can't tell either. By using a primary key this problem is solved, because both topics are unique.
All the other tables have got primary keys too and they work the same way.
user_name
This is a text field, called a VARCHAR field in MySQL. The number between brackets is the maximum length. A user can choose a username up to 30 characters long. This field cannot be NULL. At the bottom of the table you can see this field is declared UNIQUE, which means the same username cannot be registered twice. The UNIQUE INDEX part tells the database we want to add a unique key. Then we define the name of the unique key, user_name_unique in this case. Between brackets is the field the unique key applies to, which is user_name.