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.
primary key is the unique identifier for a given object. This is usually assigned the label
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).
This schema illustrates a simple database structure with
Petbelongs to an
Owner, therefore the pet has a foreign key called
owner. This foreign key maps to the primary key on the
Ownerhas one address, therefore the
Ownertable has a foreign key called
address. This foreign key maps to the primary key on the
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>can have
<One or Many>
Then think through the sentences you formed:
Onein each sentence, it is a
Onefor the two sentences, it is a
Many, it is a
In our first example, we only modeled
One-to-One (Owner <—> Address) and
One-to-Many (Owner<—>Pet) relationships. How do you make a
You need another table. The additional table holds the two foreign keys of the rows you are trying to link. It is called a
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.