Introduction to Entity-Relationship model

Relational Databases

Relational databases are the most common database type.
They are also very flexible and can be used for many different applications. Like for example, they can be used for storing data about your users.

They work with an entity-relationship model and are defined by tables and columns.

Let's take a look at the main components of a relational database.

Components of a Relational Database

Schema - The schema is the structure of the database. It defines the tables and columns. An example of the User schema is the following:

CREATE TABLE User (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT,
    password TEXT
);

Data - the actual data stored in the database. An example of the User data is the following:

id: 1

name: John Doe

email: johndoe@gmail.com

password: ajn1489valpa

Structured Query Language - the language used to query the database. It is used to select, insert, update and delete data. A few examples of SQL queries are the following:

SELECT * FROM users
INSERT INTO users (name, email, password) VALUES ('John Doe', 'johndoe@gmail.com', 'ajn1489valpa')
UPDATE users SET name = 'John Doe' WHERE id = 1
DELETE FROM users WHERE id = 1

Entity-Relationship Model

Entity: Each type of table in a database is called an entity. An entity can have different relationships with other entities.
Relationship: A relationship is a way of connecting two entities. A relationship can be one-to-one, one-to-many, many-to-one or many-to-many.

Types of relationships:

One-to-One: A one-to-one relationship is a relationship where each entity has a unique relationship with another entity. For example, a user has a unique relationship with their profile.

One-to-Many: A one-to-many relationship is a relationship where each entity has a relationship with multiple other entities. For example, a user can have many posts.

Many-to-One: A many-to-one relationship is a relationship where many entities have a relationship with one other entity. It is the opposite of a one-to-many relationship. For example, many posts can be created by a user.

Many-to-Many: A many-to-many relationship is a relationship where many entities have a relationship with many other entities. For example, many users can like many posts. Many-to-many relationships are usually stored in a join table which can be an entity in its own right.

How are relationships defined?
A relationship is defined by the foreign key. A foreign key is a column in a table that references primary key of another table. For example, a profile has a foreign key to a user.

Let's create a profiles table to demonstrate foreign keys.

CREATE TABLE profiles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

The foreign key is defined in the profiles table.
The user_id column references the id column in the users table.
This means that each profile belongs to a user.

Syntactically, we could also define the relationship in the users table.
However, semantically, it is better to define the relationship in the profiles table.
When we define a foreign key constraint, adding an item to the table will require the referenced primary key to exist. This means that the user must be created before the profile.

Let's create more tables and define one-to-many, many-to-one and many-to-many relationships.

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    title TEXT,
    body TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER,
    user_id INTEGER,
    body TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    FOREIGN KEY (post_id) REFERENCES posts(id)
    FOREIGN KEY (user_id) REFERENCES users(id)
);

posts table

In the posts table, we have a user_id column that references the id column in the users table.

This means that each post belongs to a user. But a user can have many posts.

comments table

In the comments table, the post_id column references the id column in the posts table.

The user_id column references the id column in the users table.

Comments have a many-to-one relationship with posts as well as users.
Many comments can be associated with a single post. Many comments can be associated with a single user.

The comments table also demonstrates the many-to-many relationship between posts and users.

Thanks for reading! Hope this gives you some ideas on how to define entity-relationship models in SQL. Stay tuned for more! If you want to connect with me, you can find me on Twitter @abh1navv