Relational Database Design
This page provides an overview of data model used by relational database management systems.
Most popular database management systems are relational systems, and are usually referred to as Relational Database Management Systems (RDBMS). What this means is that their databases can contain multiple tables, some (or all) of which are related to each other.
Relationships
When two or more tables contain related data, they are said to have a relationship.
Relationships are something you design. They don't happen by accident. You actually design your database in a way that determines which tables will have a relationship.
-
Example of a Relationship
This screenshot shows an example of a relationship.
The Artists and Albums tables are related. This is because the Albums table has an ArtistId column that corresponds to the ArtistId in the Albums table.
In this case, the Artists table is the parent table (or primary table), and the Albums table is the child table.
Any value stored in the Albums.ArtistId column must correspond with a value in the Artists.ArtistId column.
Doing this enables us to look up the artist's name for any given album.
How Does This Work?
The following screenshots demonstrate the data involved in the above relationship.
-
The Artists Table
Firstly, in the Artists table, each record has a primary key. A primary key is a column that stores a unique identifier for for each record.
The value of this unique identifier can be as simple as an incrementing number. So, in our Artists table, the first record has a value of 1, the second record has a value of 2, and so on.
-
The Albums Table
Secondly, when entering each album into the Albums table, instead of writing out the full artist's name in that table, we only need to add the artist's unique identifier. In this case, the unique identifier is a number, so we enter this number into the ArtistId column of the Albums table.
This column is referred to as a foreign key, because it references data from the primary key of another table.
So we can see that the first album belongs to artist 3. If we look at the Artists table, we can see that artist 3 is Iron Maiden.