Introduction to Database Design (Coding Bootcamp Series)

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):


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.


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).



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:



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.

Example of Junction Table

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!




📝 Read this story later in Journal.

🗞 Wake up every Sunday morning to the week’s most noteworthy Tech stories, opinions, and news waiting in your inbox: Get the noteworthy newsletter >

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close