Delicious Database Design: create tables and relationships with SQL

{ 0 comments }

In the previous lesson we have seen how to design the relationship-entities model for a database to be used in a del.icio.us-like web site project. Our R-E model is:

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 (
user_id_pk INT NOT NULL AUTO_INCREMENT,
user_name VARCHAR(40),
email VARCHAR(40),
password VARCHAR(20),
user_date DATE,
PRIMARY KEY (user_id_pk)
) TYPE=INNODB;

CREATE TABLE SITE (
site_id_pk INT NOT NULL AUTO_INCREMENT,
url VARCHAR(250),
description LONGTEXT,
share_data DATA,
PRIMARY KEY
) TYPE=INNODB;

CREATE TABLE SHARE (
share_id_pk INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
site_id INT NOT NULL,
submitted_by INT NOT NULL DEFAULT 0,
PRIMARY KEY (share_id_pk),
FOREIGN KEY (user_id) REFERENCES USER(user_id_pk) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (site_id) REFERENCES SITE(site_id_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

Our database is now ready and we can implement it using JSP, PHP and MySQL