Designing Database : Database Relationships

관계형 데이터베이스에서 Relationship의 3가지 타입을 정리하였습니다.

 

 

---

< Introduction >

What are relationships? A database relationship establishes the way in which connected tables are dependent on one another.

What are the different types of database relationships? There are three types: one-to-one, one-to-many and many-to-many.

 

&amp;amp;amp;amp;lt; Sample database schema diagram &amp;amp;amp;amp;gt;

: The lines between tables connect foreign keys and primary keys.

 

 

---

< One-to-One Relationship >

In a one-to-one relationship, a row of table A is associated with exactly one row of table B and vice-versa.
For example, A driver may only have one driver’s license issued to them and vice-verse.

 

Example : a driver table and a license table.

To establish a one-to-one relationship in PostgreSQL between these two tables, we need to designate a primary key and foreign key in one of the tables.

&amp;amp;amp;amp;lt; Database Schema &amp;amp;amp;amp;gt;

CREATE TABLE driver (
    license_id char(20) PRIMARY KEY,
    name varchar(20),
    address varchar(100),
    date_of_birth date
);      
 
CREATE TABLE license (
    id integer PRIMARY KEY,
    state_issued varchar(20),
    date_issued date,
    date_expired  date,
    license_id char(20) REFERENCES driver(license_id) UNIQUE
);

: To enforce a strictly one-to-one relationship in PostgreSQL, we need another keyword, UNIQUE

 

 

---

< One-to-Many Relationship >

A one-to-many relationship is with two tables - a parent and a child table.

Analogous to a parent-child relationship where a parent can have multiple children, a parent table will house a primary key and the child table will house both primary and foreign keys. The foreign key binds the child table to the parent table.

 

Example : 

consider a table where we want one person to be able to have many email addresses. However, if there is a primary key in the table, such as id, the following rows will be rejected by the database.

name   id (PK)     email       
Cody   2531       cody@yahoo.com 
Cody   2531       cody@google.com
Cody   2531       cody@bing.com

To resolve this, we need to use one-to-many relationship between person and email tables.

So, one person can have many email addresses.

 

 

---

< Many-to-Many Relationship >

A many-to-many relationship can be broken into two one-to-many relationships.

To implement a many-to-many relationship in a relational database, we would create a third cross-reference table also known as a join table.

 

Examples of many to many relationships :

• A student can take many courses while a course can have enrollments from many students.
• A recipe can have many ingredients while an ingredient can belong to many different recipes.
• A customer can patronize many banks while a bank can service many different customers.

 

< Join Table >
A join table have these two constraints :

• foreign keys referencing the primary keys of the two member tables.
• A composite primary key made up of the two foreign keys.
  Or, you can use another unique primary key in join table.

 

Example : A recipe and An ingredient table.

A third cross-reference table, recipes_ingredients, will support the following columns:

• recipe_id (foreign key referencing recipe table’s id)(primary key)
• ingredient_id (foreign key referencing ingredient table’s id) (primary key)

CREATE TABLE recipes_ingredients (
  recipe_id varchar(50) REFERENCES recipe (id),
  ingredient_id varchar(50) REFERENCES ingredient (id),
  PRIMARY KEY (recipe_id, ingredient_id)
);

 

 

Reference : Design pattern - many-to-many relationship

Database Design - Many-to-many