A correct approach to define relationships between database's tables

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)

... 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)

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;

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

For other about this argoument visit the following links:
Project a database: define the relationships-entities model (tables, attributes, and relationships )
Project a database: create tables and relationships with SQL
Project a database: how to use PHP and SQL to create tables and relationships

... or take a look at the table of contents.