Introduction to Entity-Relationship model
Tags: sql, beginners, codenewbie, database
Published: 2021-11-23 16:42:06


## 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:
```SQL
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:
```SQL   
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:
1. **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.
2. **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.
3. **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.
4. **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.
```SQL
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.
```SQL
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](https://twitter.com/abh1navv)