I dedicated already some post about how to design and develop a database, but some readers asked to me to explain how to define relationships in a database and a correct approach to create them.
In general a relationship is defined between a foreign key and a primary key of two - or more - different tables. An example can be the following: an user lives in a city (or in a city live more users). The relationship-entity model for this simple case is something like this:
Generally, to optimize the database, it's a good rule to define a relationship using two INTEGER type fields. For example if you have 3 cities into the CITY table identified from ID_CITY_PK (primary key):
ID_CITY_PK (1)
CITY (Paris)
ID_CITY_PK (2)
CITY (New York)
ID_CITY_PK (3)
CITY (Rome)
CITY (Paris)
ID_CITY_PK (2)
CITY (New York)
ID_CITY_PK (3)
CITY (Rome)
... and a record into the USER table with ID_CITY (foreign key) = 3, this relation associates the current record to the city with ID_CITY_PK = 3 into the CITY table, that is Rome.
ID_USER_PK (1)
NICK (Antonio)
...
ID_CITY (3)
NICK (Antonio)
...
ID_CITY (3)
How to define relationships using phpMyAdmin
If you use a database MySQL and phpMyAdmin to manage it, select a table (in this example USER) and click on "Relation View", below the table's fields:
Now, from the foreign key field in the table USER (id_city) the field with which you want define the relationship, in this case id_city_pk in the CITY table:
Click on save to save created relation and repeat the action for all tables require relations.
If you use a Database Access, relationships can be defined from relationship view simply connecting, just with a mouse's click, two field.
How to define relationships using SQL
I suggest to use always a separated file with SQL statements to create all database's tables and relationships between them. The code is very simple, easy to modify and to reuse. A relation is defined using the statement REFERENCES:
CREATE TABLE CITY (
id_city_pk INT NOT NULL AUTO_INCREMENT,
city VARCHAR(100),
PRIMARY KEY (id_city_pk)
) TYPE=INNODB;
CREATE TABLE USER (
id_user_pk INT NOT NULL AUTO_INCREMENT,
nick VARCHAR(40),
email VARCHAR(40),
password VARCHAR(20),
id_city INT NOT NULL,
PRIMARY KEY (id_user_pk)
FOREIGN KEY (id_city) REFERENCES CITY(id_city_pk) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=INNODB;
id_city_pk INT NOT NULL AUTO_INCREMENT,
city VARCHAR(100),
PRIMARY KEY (id_city_pk)
) TYPE=INNODB;
CREATE TABLE USER (
id_user_pk INT NOT NULL AUTO_INCREMENT,
nick VARCHAR(40),
email VARCHAR(40),
password VARCHAR(20),
id_city INT NOT NULL,
PRIMARY KEY (id_user_pk)
FOREIGN KEY (id_city) REFERENCES CITY(id_city_pk) ON UPDATE CASCADE ON DELETE CASCADE
) TYPE=INNODB;
Remeber the order is very important when you create your database: first, you have to create table without dependancies and then all others.
Related Posts
I am Antonio Lupetti, Engineer, Pro Blogger, Mac user, Web addicted.
Rome, IT.
Sponsored Links
Share this post
Old Comments
Thanks for this useful information!
Here's a link explaining how to enable Relation View on Ubuntu.
Hey, Good tutorial,
One question though
What is the advantage of setting up these relationships in sql?
My PHP code knows what relationships there are and updates whatever tables are necessary.
I may be doing things horribly wrong but had never heard of setting up relationships in mysql till i had read your post.