Skip to content

What is a relational database?

When you start building an application, one of the most important decisions that you must make is whether to go for a relational database or a non-relational database. A good understanding of the data used within your application is key in making this decision. A relational database stores data in tables and uses relationships to retrieve and make changes to the data. By the end of this article, you should have a good understanding of relational databases and when to use them.

Structuring a relational database

As mentioned earlier, a relational database stores data in tables. Each table is made up of rows and columns. The columns specify an attribute and each row consists of one entry of the entity. Now, let’s create a simple database that stores student data. It consists of two tables that store student details and their contact info. The main table here would be the STUDENT table. It can contain the basic details of a student like their first name, last name, age, date of birth, and enrollment date. But, how do you differentiate between students with the same data for different fields? That’s where a primary key comes into the picture. A primary key helps in uniquely identifying each row of a table. For the above example, it can be an ID that is unique to each student. Take a look at the diagram below to understand it better. Now, what if you want to store a student’s contact information? One approach would be to add columns to the existing table. While this is good for small amounts of data, it is not recommended to keep adding additional attributes as it can lead to performance degradation. To resolve this, we create another table called CONTACT_INFO with the following attributes. Notice how we have a student_id associated with each row. This is an example of a one-to-one relationship. This means that each student has one entry in the CONTACT_INFO table, and each entry in the CONTACT_INFO table is related to only one student. Read more about what a relational database is and how to design entities.

Creating a relational database in MySQL

One of the most popular relational databases in the world is MySQL. For this example, we are going to use a PlanetScale database to demonstrate. You can sign up using a GitHub account or just use an email and password. See the PlanetScale quickstart guide to learn more. Once you’re in, create a new database called “student_management_system” and create the following two tables. You can use the “Console” tab to execute these queries.

CREATE TABLE STUDENTS (

  id int NOT NULL PRIMARY KEY,

  first_name varchar(255) NOT NULL,

  last_name varchar(255) NOT NULL,

  age INT NOT NULL,

  date_of_birth DATE NOT NULL,

  enrollment_date DATE NOT NULL

);




CREATE TABLE CONTACT_INFO (

 student_id int NOT NULL,

 city varchar(255) NOT NULL,

 phone varchar(45) NOT NULL,

 guardian_name varchar(255) NOT NULL

);

Common key constraints

You can use the following key constraints while creating a table in MySQL. In the example above, you can see two of them already.

  • PRIMARY KEY: The selected column becomes the unique identifier for the table. You can identify each row uniquely using the value in this column.
  • NOT NULL: Disallows null values for the selected column.
  • UNIQUE: Allows unique values only for the selected column.
  • FOREIGN KEY: Marks the selected column as a foreign key in the current table. Foreign keys refer to primary keys of another table and are used to create relationships.

After creating the tables, insert data using these two queries:

INSERT INTO STUDENTS (id, first_name, last_name, age, date_of_birth, enrollment_date)

VALUES

(1341, "Leo", "Collins", 17, "2006-04-12", "2023-05-01"),

(1342, "Cindy", "Miles", 18, "2005-11-03", "2023-05-01"),

(1343, "Leo", "Robertson", 17, "2006-09-09", "2023-05-01");

INSERT INTO CONTACT_INFO (student_id, city, phone, guardian_name)

VALUES

(1341, "Seattle", "206-203-8253", "Carmen Griffin"),

(1342, "Austin", "512-671-0008", "Clyde Lowe"),

(1343, "Boston", "508-810-1556", "Jon Nguyen");

To retrieve the student data along with their contact details, we can use the query below:

SELECT * FROM STUDENTS, CONTACT_INFO

WHERE STUDENTS.ID = CONTACT_INFO.STUDENT_ID;

See how easy it is to set up and get started with PlanetScale? From signing up to running queries, we did everything in about five minutes. Standard MySQL installations take much longer. The above query retrieves data using the relationship between the two tables. It uses the student ID to form the relationship. There are other kinds of relationships like one-to-many, many-to-one, and many-to-many. See the example diagrams below to understand these relationships.

  • ONE-TO-MANY: One teacher can have multiple teaching assistants. Each entry in the TEACHING_ASSISTANT table will have the unique ID of the teacher.
  • MANY-TO-ONE: Multiple modules are part of a single department. Each entry in the MODULE table will have the unique ID of the department. Note that ONE-TO-MANY and MANY-TO-ONE are the same, but it depends on the entity being queried upon.
  • MANY-TO-MANY: Each student can enroll in multiple modules and each module can have multiple students enrolled in it. In this case, we cannot form a relationship with just two tables. So, we use a third table to decouple the many-to-many relationship.

SQL vs NoSQL – What is the difference?

So far, we’ve looked at storing data in tables and using relationships to query them. You can use relational databases when the data is structured. All relational databases use SQL (Structured Query Language) to query and modify records. Remember how we created a table using the CREATE TABLE query on PlanetScale? This is SQL and it defines the structure of a table and ensures data integrity because we have also mentioned the types expected for each attribute. What if the data in your system does not have a rigid structure? In that case, we opt for NoSQL databases. There are different kinds of NoSQL databases that store data in the form of documents, key-value stores, graph databases, and more. It lacks the rigidity of a SQL database and is more flexible for storing large amounts of data. But on the other hand, data integrity is not as strong as with SQL databases.

When to choose a relational database

Relational databases have stood the test of time and are widely used at top tech companies. You can choose a relational database by asking yourself a few questions:

  • Is the data in your system structured and related?
  • Does the data remain consistent in its structure?
  • Do you want to use SQL?
  • Do you want to perform complex queries on the structured data?
  • Is data integrity a concern for you? If you answered yes to most or all of the above questions, a relational database is an obvious choice for you. You can get started with PlanetScale and prototype your application to see if a relational database fits your requirements.

Benefits of using a relational database

One of the benefits of using relational databases is the ease with which you can visualize a model with its real-life counterpart. In the examples above, STUDENT, CONTACT_INFO, MODULE, TEACHER, and TEACHING_ASSISTANT are real-life entities that are easy to visualize and understand. Each attribute from these tables gives you an idea of what they mean for that particular entity. Relational databases allow you to perform complex queries on structured data. Since we are creating tables with a structure, there is no chance for data of different types to be inserted. Relational databases follow the ACID (Atomicity, Consistency, Isolation, and Durability) properties for transactions. This ensures reliability and data integrity.

ACID properties

  • Atomicity: Each transaction (create, delete, read, update) is a single unit. If a transaction fails, the system rolls back to the initial state. If the transaction succeeds, the system moves to a new state. There is no state in between.
  • Consistency: Only expected changes to the data are allowed. Isolation: Each transaction takes place as if it’s the only one. Concurrent transactions don’t interfere with one another.
  • Durability: A successful transaction results in data changes even if there’s a system failure.

Conclusion

Relational databases are best suited for structured and related data. It is a mature technology and is widely used in many of the core sectors. You can get started today with one of the top relational databases, MySQL, through PlanetScale. Be it a hobby project or the next billion-dollar business, PlanetScale has an offering for you. Sign up today. Never worry about another 3 am wake-up call saying the site is down. Give your engineers the power they deserve with a PlanetScale database today. Get started now