Two popular type of of databases are Relational Databases
(ex: postgres, mysql, sqlite) and Non-relational databases
(ex: mongoDB). Each has advantages. Most people start with Relational Databases, because they have a lot of advantages out of the box, and fit the majority of use cases.
A relational database has many different tables. The tables are linked together through primary keys
and foreign keys
. Tables typically correspond with classes or relationships in your code.
A primary key
is the unique identifier for a given object. This is usually assigned the label id
or pk
.
A foreign key
is located in one table to link it to another table. The foreign key that is stored in one table is linked to the primary key in another table.
Each table has multiple roles. You can think of the table corresponding to a class
and a row in the table corresponding to an instance
of the class. (Table is the Cow
model, row is a single cow).
column | role |
---|---|
id | primary key |
name | attribute |
age | attribute |
address | foreign key |
column | role |
---|---|
id | primary key |
name | attribute |
breed | attribute |
owner | foreign key |
column | role |
---|---|
id | primary key |
street_address | attribute |
zipcode | attribute |
state | attribute |
This schema illustrates a simple database structure with Owner
, Pet
, and Address
.
Pet
belongs to an Owner
, therefore the pet has a foreign key called owner
. This foreign key maps to the primary key on the Owner
tableOwner
has one address, therefore the Owner
table has a foreign key called address
. This foreign key maps to the primary key on the Address
table.There are several types of relationships you can have in a database. Here are the most common:
The names are pretty self-explanatory, but lets look at some examples of each.
:whitecheckmark: Decide which relationship best matches each of the following examples:
Here is a good way to think about these relationships: fill in the blanks in the following sentences:
<Model #1>
can have <One or Many>
<Model #2>
<Model #2>
can have <One or Many>
<Model #1>
Then think through the sentences you formed:
One
and One
in each sentence, it is a One-to-One
relationshipMany
and One
for the two sentences, it is a One-to-Many
relationshipMany
and Many
, it is a Many-to-Many
relationshipIn our first example, we only modeled One-to-One
(Owner <β> Address) and One-to-Many
(Owner<β>Pet) relationships. How do you make a Many-to-Many
relationship?
You need another table. The additional table holds the two foreign keys of the rows you are trying to link. It is called a Join Table
.
column | role |
---|---|
id | primary key |
name | attribute |
specialty | attribute |
years_experience | attribute |
column | role |
---|---|
id | primary key |
name | attribute |
ssn | attribute |
ethnicity | attribute |
column | role |
---|---|
id | primary key |
doctor | foreign key |
patient | foreign key |
In this example, a Patient
can have multiple Doctors
and a Doctor
can have multiple Patients
. So, we need a join table. Typically, join table names are a concatenation of the each of the individual table names. In this case, DoctorPatient
. The join table is very simple. It contains an id
that identifies the linkage. Then it has two foreign keys, one pointing to the Doctor
and another pointing to the Patient
in the relationship.
Resources: