Database design
Tags: database, sql, mongodb, redis
Published: 2020-07-28 07:07:42

Database design is structuring data and relationships in a database. It goes beyond how the database holds data. 
In this series of posts, we would look into design techniques for both SQL and NoSQL. We would design databases for 
1. E-commerce Stores
2. School management
3. News sites

We would see how these databases can be designed on diagrams and then converted to SQL and NoSQL. We would learn about choosing the right database type. Exciting right? 

## Basics
The basics of database design is identifying what data needs to get stored, grouping such data into tables or collections and adding relationships between related data points. The whole database design process involves
1. Using Entity-Relationship (ER) diagrams to visualize the objects that get stored and their relationships.
2. Using modern database design uses tools such as 
    * [Quick DB](https://www.quickdbd.com/) for SQL and
    * [Hackolade](https://hackolade.com/index.html) for NoSQL

to build tables or collections which can be exported into syntax for the specific platform (Postgresql, MongoDB, MySQL, etc).

## SQL, NoSQL? What's the difference?
I'm glad you asked. 

### SQL Databases.
A SQL database is one that manipulates data using the `Structured Query Language` (SQL). SQL databases store data in tables. A table looks something like

**people**                                                       

| id |     name     | age |           job                       |
|:----:|:--------:|:---:|:-------------------------:|
| 1  | John Doe | 45  | name used on the internet |
| 2  | Jane Doe | 40  | supposed female version   |
| 3  | Josh Doe | 1   | developer                 |

So, if we want to get the names of the people in our table, we would use SQL like so:

```sql
SELECT name FROM people;
```
This is called a SQL query. A query is a command that basically Creates, Reads, Updates and Deletes (CRUD) data in a database. The SQL query above retrieves the names of the people in the people table. So a SQL database operates using SQL queries.
If you're wondering, the result of the query above would be:

| names    |
|----------|
| John Doe |
| Jane Doe |
| Josh Doe |

Examples of SQL databases
1. [PostgreSQL](https://www.postgresql.org/)
2. [MySQL](https://www.mysql.com/)
3. [Microsoft SQL Server](https://www.microsoft.com/en-gb/sql-server/sql-server-downloads)

### NoSQL databases
A NoSQL database is one that doesn't use SQL for queries. An example is a document database like MongoDB. MongoDB stores data in documents. A representation of our user table in a document database would be

```JSON
{
  "people": [
    {
      "id": 1,
      "name": "John Doe",
      "age": 45,
      "job": "name used on the internet"
    },
    {
      "id": 2,
      "name": "Jane Doe",
      "age": 40,
      "job": "supposed female version"
    },
    {
      "id": 3,
      "name": "Josh Doe",
      "age": 1,
      "job": "developer"
    }
  ]
}
```
Querying a document database would look similar to:
```shell
   db.people.find({}, { "name": 1, "_id": 0 })
```
Other examples of a no SQL databases would be a 
1. Graph databases like this one
![graph db.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1595915650677/yKxAezwBF.png)
Here, we can see how one person is a friend of another who likes something else. It shows how deeply related certain things are. It's a good design for social media software.

2. Key-value stores like [Redis](https://redis.io/). 

## Entity-Relationship (ER) diagrams
These diagrams are used to represent data objects and relationships using shapes and arrows. ER diagrams are an important step to database design. Below is a typical ER diagram.

![er diagram.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1595376660878/9EbOilkFn.png)

Here, we have a database with three tables (or collections) and two relationships. Even if you have no idea of databases, the intuitive structure of an ER diagram gives you an idea of the information the database deals with.
* The ovals connected to `Customer`: `customer-no`, `name` and `e-mail` are called attributes. In SQL, they are columns. In a document database, they are BSON (binary JSON) properties.
* The rectangles represent entities or objects. In SQL, they are the tables. In a  document database, they are collections.
* The rhombuses are relationships. In this ER diagram, `makes` connects `customer` to `order`. So a `customer` `makes` an `order`. `includes` connects `order` to `product`. So an `order` `includes` a `product`. 

With an ER diagram, you can have a complete overview of how the database would be implemented for the specific platform (SQL or NoSQL). 

## SQL DB Modelling tool (QuickDB)
[QuickDB](https://www.quickdbd.com/) can be used model tables and relationships for multiple Database Management Systems (DBMS). It's a freemium product which means it has a free and paid plan. It's most exceptional feature is exporting to SQL specific code for multiple platforms including MySQL and Postgresql.

![quickdb.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1595398724299/_pCA8uI8u.png)

## Wrap up
Modern databases are designed using modern tools. Before starting a project using a database, you must design your database to prevent confusion and bad app design down the project's roadmap. 

Do share this article if you found it useful. It's a series so expect more posts under database design. Just for fun, enjoy this image

![fun image comparing databases.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1595913701959/YWcVvLsMc.png)


😂 I hope you laughed a little. Thanks for reading. Adios ✌🏽🧡

## Credits
1. Cover photo by [fabio](https://unsplash.com/@fabioha) on [Upslash](https://unsplash.com/s/photos/database)
2. NoSQL - SQL comparison from [this blog](https://highlyscalable.wordpress.com/2012/03/01/nosql-data-modeling-techniques/)
3. Graph database image from [Neo4J](https://www.bmc.com/blogs/neo4j-graph-database/)