2. Tables, Entities, and RDBMS

In a database design design we are discuss these three important topics - Tables, Entities and RDBMS. You may ask why ? Aren’t you digressing from the topic ?

I would like to say that we would be using these concepts as the building blocks of our design understanding. These concepts are highly intertwined with database design and we will be dealing with them closely. I am introducing them here so that we can learn database design easily later at a quicker pace.

What is a Relational Database ?

A relational database is a type of database that stores data in tables, and these tables are related to each other with the use of keys. The relational database tables is like a smart Microsoft excel with multiple sheets. If you look at these sheets you will find :

• Each table holds data about a specific thing (Example: Users, Products, Orders)

• Each row is a record (like one user or one order)

• Each column is a property (like name, email, date)

• The relationships between tables are defined using primary keys and foreign keys

What is an Entity?

An entity is any real-world object or concept that you want to store data about.

Examples: User,Product or Owner.

Each entity has properties (attributes).

For example a User entity may have the attributes : name, email and password.

What is a Table ?

In relational database each entity becomes a table.

A Table is made up of:

• Rows (Records) → each row is a single entry (e.g., one user)

• Columns (Fields) → each column is a piece of data (e.g., email)

Let us visualise the user table below with two records inserted

The user is advised to create an account on any popular sql learning website and visualise the sample tables with the above one.

The RDBMS table resembles a spreadsheet. Here you have a User table with two records or rows. Each record has four columns which tells us about each individual user.

We will learn more about creation of tables and SQL in a later chapter.

What is RDBMS ?

RDBMS stands for Relational Database Management System.

It is a software that:

• Stores data in tables.

• Allows relationships between tables.

• Supports SQL to manage data.

• Maintains data integrity, security, and consistency.

Popular RDBMS examples include:

• MySQL

• PostgreSQL

• Oracle

• Microsoft SQL Server

• SQLite

• MariaDB

In RDBMS, we can connect data from different tables using relationships:

• One-to-One (1:1) – example: each user has one profile

• One-to-Many (1:N) – example: one user can place many orders

• Many-to-Many (M:N) – example: there are multiple products in many orders, and multiple orders with many products.

Again, we will discuss these nuances in depth later.

Primary Key and Foreign Key

Primary Key (PK): A unique identifier for a row (e.g., user_id)

Foreign Key (FK): A column that connects to another table’s primary key.

Let us visualise the two keys in the diagram below.

The above image contains the schema (discussed in chapter 1). There are two tables Users and Orders.

Each user is identified by a primary key (user_id) in the above use-case. There is a key symbol 🔑 next to the user_id text which tells us that this is a primary key.

Similarly, each order is characterised by its primary key order_id 🔑.

The two tables are “linked” by a line. Please observe carefully.

The line tells us that Orders.user_id is a foreign key which “references” The Users.user_id which is a primary key. In layman terms this means, that one user can have many orders. But each order belongs to one and only one user. This in RDBMS parlance means a One-to-many relationship.

The line is like a connector that says, “This user_id in the Orders table must match a user_id in the Users table.”

Thus we can understand that a foreign key is a column in one table that links to the primary key in another table.

Why is a Foreign Key Required ?

• Maintains Data Consistency - Ensures that every order is connected to a valid user. We can’t have an order with a user_id that doesn't exist in the Users table.

• Maintains Data Integrity - Enforces rules in the database automatically. If we try to insert a row in Orders table with an invalid user_id, the database will reject it.

• Helps With JOINs and Relationships - Makes it easy to combine data from multiple tables using SQL joins. In a future chapter, we will learn this concept in depth.

• Prevents Mistakes - Avoids accidentally deleting a user who still has orders unless rules are specified.

• Improves Query Performance (via Indexes) - Databases often index foreign keys, making queries faster.