This is a mini-guide with hot tutorials to
learn, step-by-step, how to design a database from
relationships-entities model to final implementation using SQL, MySQL
and PHP. If you are a newbie to design database you can find it very
useful.
DATABASE DESIGN FUNDAMENTALS
This is a mini-guide with hot tutorials to learn,
step-by-step, how to design a database from relationships-entities
model to final implementation using SQL, MySQL and PHP.
If you are a newbie to design database you can find it very useful.
This lesson explains how to project a typical relationships-entities model for a database to be used in our web projects. My approach is:
- 1. Define database entities (tables)
- 2. Identify attributes for tables
- 3. Define relationships and cardinality between the istances (records) of tables
Step 1: define database entities
The first step when you project a database is to identify all entities (tables). For example if we want to project a simplified del.icio.us-like web site, our database will have these entities:
- - USER (to store data about users, email, password, nickname,...)
- - SITE (to store data about the sites added by the users)
These
are only the main entities required from our project but, take a mind,
that we will add other tables to store data about relationships between
istances (records) of these tables in case of cardinality (M:M), many to many (see Step 3).
Step 2: define attributes
The next step is to define attributes for the tables USER and SITE. In this semplified example we will have something like this:
USER
-----------
id_user_pk (Primary Key)
nick
email
password
user_data_reg (user signup date)
SITE
-----------
id_site_pk (Primary Key)
site_url
site_description
site_data_reg (when a site is added)
totale_share_user (total number of users that share a site)
Step 3: define database relationships
Our
simple application del.icio.us-like works in this way: an user add a
site that can be shared by other users. The relationship's cardinality
beetwen USER table and SITE table is:
USER > SITE (M:M) - Many to Many (an user can add many sites).
SITE > USER (M:M) - Many to Many (a site can be shared by many users).
In
this case ( cardinality M:M) we have to add a new table (SHARE) that
contains all possible combinations between all instances of USER table
and SITE table . In this new table, SHARE, to identify an user that
share a site added by another user or by itself, we will add two Foreign Key:
SHARE
-----------
id_share_pk (Primary Key)
id_user (Foreign Key > USER)
id_site (Foreign Key >SITE)
submitted_by (boolean: flag only if the current user has submitted the site)
Implement your database using SQL
Now, our database is ready to be implement with a DBMS (for example using MySQL).
Now we implement the database using SQL and phpMyAdmin. We crate a
new database on phpMyAdmin and select the "SQL" tab. Copy and paste
this SQL code into the form and click on execute button:
CREATE TABLE USER (
id_user_pk INT NOT NULL AUTO_INCREMENT,
nick VARCHAR(40),
email VARCHAR(40),
password VARCHAR(20),
user_reg_date DATE,
PRIMARY KEY (id_user_pk)
) TYPE=INNODB;
CREATE TABLE SITE (
id_site_pk INT NOT NULL AUTO_INCREMENT,
site_url VARCHAR(250),
site_description LONGTEXT,
site_data_reg DATA,
PRIMARY KEY
) TYPE=INNODB;
CREATE TABLE SHARE (
id_share_pk INT NOT NULL AUTO_INCREMENT,
id_user INT NOT NULL,
id_site INT NOT NULL,
submitted_by INT NOT NULL DEFAULT 0,
PRIMARY KEY (id_share_pk),
FOREIGN KEY (id_user) REFERENCES USER(id_user_pk) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (id_site) REFERENCES SITE(id_site_pk) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=INNODB;
Create Relationships
To create relationships between database's table (for example between SHARE table and the other tables) you have to use the SQL code below:
FOREIGN KEY (attribute_name_1) REFERENCES tableOfReference(attribute_name_2)
where attribute_name_1 is the foreign key (generally, a field of type INTEGER)a and attribute_name_2 the primary key of the table of destination.
To force the referencial integrity between the data of database, you have to add this code:
ON UPDATE CASCADE ON DELETE CASCADE
Step 1: add a DB folder into the site structure
In the site root we create a new folder DB and add a file create_database.php into this folder.
Step 2: create the php file
Open create_database.php on Dreamweaver and switch to Code View. Copy and paste this code inside the <body> tag.
<?php
//Connect to database
include('../config.php');
// CREATE TABLE USER
$sql= "CREATE TABLE USER (
id_user_pk INT NOT NULL AUTO_INCREMENT,
nick VARCHAR(40),
email VARCHAR(40),
password VARCHAR(20),
user_reg_date DATE,
PRIMARY KEY (id_user_pk)
) TYPE=INNODB";
mysql_query($sql);
// CREATE TABLE SITE
$sql="CREATE TABLE SITE (
id_site_pk INT NOT NULL AUTO_INCREMENT,
site_url VARCHAR(250),
site_description LONGTEXT,
site_data_reg DATA,
PRIMARY KEY
) TYPE=INNODB";
mysql_query($sql);
// CREATE TABLE SHARE
$sql="CREATE TABLE SHARE (
id_share_pk INT NOT NULL AUTO_INCREMENT,
id_user INT NOT NULL,
id_site INT NOT NULL,
submitted_by INT NOT NULL DEFAULT 0,
PRIMARY KEY (id_share_pk),
FOREIGN KEY (id_user) REFERENCES USER(id_user_pk) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (id_site) REFERENCES SITE(id_site_pk) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=INNODB";
mysql_query($sql);
// Close Connection
mysql_close($db_con);
?>
Now save the file and test it on localhost. Remember to include in create_database.php, connection's info (CONFIG.PHP) to your database.
|
