Delicious Database Design: create tables and relationships with SQL
Wall Script
Follow Me:
Tuesday, November 18, 2008

Delicious Database Design: create tables and relationships with SQL

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
Was this article helpful?
Thanks! Your feedback helps us to improve 9lessons.info


0 comments:

Make in India