Introduction to Database Design - Coding Bootcamp Series

Diana Bernardo

As I approach the end of my programming bootcamp at Green Fox Academy, I bring you something I have not mentioned yet but that is essential for any backend programmer: databases and relationships.

A database is a collection of tables, with each of them containing data about a different, but related subject. A table is composed of records and fields that contain data. Each row in a table is a record and each column is a field of that record.

In a relational database — one where data is related to other information across databases — it is necessary to uniquely identify each record on a table. That is achieved through the use of a Primary Key. A Primary Key is a column, or combination of columns, that is unique to each record and cannot contain null values, allowing each record to be properly identified. A Primary Key can be a String (example: an email address) or, as in most cases, a number, most likely a Long. In the latter case, this can be auto-generated, leaving out the worry to the developer to create a unique ID for each record. For that, we would do something like this (example taken from our project):

https://img.particlenews.com/image.php?url=46sAkK_0Z7j2Fsx00

Also, notice the @Entity annotation. It is a specialisation of the @Component annotation, informing that this class is an Entity, that is, a table in the database.

Like we said before, the data in the different tables in a database is related. So, it comes as no surprise that sometimes we will want to link two tables together, and we do this with a Foreign Key. A Foreign Key is a field in one table that refers to a Primary Key in another table. We will look into this in more detail when we address the One-to-Many relationship.

So, now that we know that tables have relationships between them, let’s look into these relationships in more detail. There are three types of relationship: One-To-One, Many-To-One/One-To-Many and Many-To-Many.

One-To-One

This type of relationship happens when an entity instance exists in a table and it is connected to another entity instance, but only to one. We do not have any case like this in our project but, let’s imagine another example… In a school, a student has a student ID and this this relationship is unique: the student can only have one student ID and that student ID can only belong to one student.

One-To-Many

This kind of relationship exists when an entity instance in one of the tables can be associated with multiple records in the other table. For example, in our project, a Badge has a @OneToMany relationship with the BadgeLevel because one Badge can have many levels.

https://img.particlenews.com/image.php?url=17cipQ_0Z7j2Fsx00

On the other hand, a BadgeLevel has the reverse of the @OneToMany relationship, that is, the @ManyToOne because many BadgeLevels relate to one Badge only. And this is where we make use of the Foreign Key we mentioned above, creating a column for it on our BadgeLevel table (badge_id).

https://img.particlenews.com/image.php?url=43BlAy_0Z7j2Fsx00

https://img.particlenews.com/image.php?url=1RfjPg_0Z7j2Fsx00

Many-To-Many

Lastly, let’s have a look at the most complex relationship, the Many-To-Many. For example, in our project, an Archetype (an Entity) has a List of Badge Levels because it can have many of them. On the other hand, the Badge Levels can be part of many Archetypes. This is a Many-To-Many relationship, because both sides can have multiple instances of the other. This is how the code looks like:

https://img.particlenews.com/image.php?url=3LUMjd_0Z7j2Fsx00

https://img.particlenews.com/image.php?url=1LxawK_0Z7j2Fsx00

In reality, a Many-To-Many relationship is, in fact, two One-To-Many relationships. In this case, one Archetype can have many BadgeLevels and a BadgeLevel can correspond to many Archetypes. To represent this, a Many-To-Many relationship will generate a junction table, composed of the Primary Keys of both the Entities referenced. And it is this combination of keys that is unique (or, as we might consider it, the Primary Key of this table). And that is what we’re defining with the @JoinTable and @JoinColumn annotations on the code above.

https://img.particlenews.com/image.php?url=1GQTo4_0Z7j2Fsx00

Lastly, I would like to remind you, as in my last article, that I am working in this project in a team and everything I wrote here is a result of not only my work but, very importantly, of my team mates’ as well.

Check back in two weeks for the last article while I am still attending the bootcamp!

Comments / 0

Published by

Travel addict writing about the wonders of the world. Visited 30+ countries, lived in 4.

466 followers

More from Diana Bernardo

Comments / 0