Skip to content

Latest commit

 

History

History
98 lines (63 loc) · 5.06 KB

db_relationship_types.md

File metadata and controls

98 lines (63 loc) · 5.06 KB
Raw

Relationships between tables (table rows)

In general the following relationships exist between (group of) rows in a first table and (group of rows) in a second table.

Types of relationships

one-to-one (and one-to-zero)

Example: a person has a passport:

  • a person has either zero or one passport (never more than one)
  • a passport belongs to exactly one person

one-to-(m)any or many-to-one

Example: a student has a mentor:

  • a student has exactly one mentor
  • a mentor supervises any number (0,1,2,...) of students

(m)any-to-(m)any

Example: A student participates in a course:

  • a student participates in any number of courses
  • a course has (m)any participating students

Diagram notation

Relationship arrows

Representation of relationships

A foreign key is a column containing values of a primary key column of the referred table.

zero/one-to-(m)any

(m)any-to-zero/one relationship

Let's add to the table A a single column of foreign key referring to B.

Then one A refers to:

  • exactly one B (when the foreign key must not be NULL);
  • zero or one B (when the foreign key can be NULL).

Zero, one or more B might refer to the same A.

(m)any-to-(m)any

(m)any-to-(m)any

Let's add a separate association table to the database.
The association table contains two foreign keys referring to the tables to be associated.
NULL foreign keys are not allowed in the association table.

Database schema

A database schema defines:

  • tables and relations present in the database
  • fields present in each table
  • constraints that apply to the data
  • physical representation of the data

The schema might be provided in different forms:

  • as code
  • as a schema diagram (entity-relationship) diagram

Data modeling is the process of creating a database schema.

Entity relationships diagram of the example database

Let's study the individual arrows between various entities in the diagram from the SQLite Sample Database:

The following sentences always have the form: ENTITY-A RELATION-NAME ENTITY-B.

Examples of zero/one-(m)any from the diagram:

  • An album is authored by exactly one artist. An artist authors zero or more albums.
  • An album contains zero or more tracks. A track belongs to zero or one album (so, the same track can't belong to several albums).
  • An invoice is always issued to exactly one customer. A customer might have zero or more invoices.

And an example of representation of many-many relationship invoice-track:

  • An invoice needs to represent selling of one or more tracks.
  • A track might be sold in any number of invoices.
  • The many-many associations between invoices and tracks are represented in invoice_items table.
  • One invoice_item always represents one track.
  • An invoice has ?zero? or more invoice_items.
  • A track belongs to zero or more invoice_items.
  • An invoice_item always belongs to exactly one invoice.

Note, a zero/one-(m)any relationships might also associate entities of the same table:

  • An employee view: I report to zero/one employee (my supervisor).
  • A supervisor view: zero or more employees report to me.

Drawing an own relationships diagram

The diagram editor might be used for free to draw and save/load own database diagrams.

Here is an example of an incomplete diagram.