Database design

#database #sql #mongodb #redis

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

E-commerce Stores
School management
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

Using Entity-Relationship (ER) diagrams to visualize the objects that get stored and their relationships.
Using modern database design uses tools such as
- Quick DB for SQL and
- Hackolade 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:

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

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

{
  "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:

   db.people.find({}, { "name": 1, "_id": 0 })

Other examples of a no SQL databases would be a

Graph databases like this one

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.
Key-value stores like Redis.

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.

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 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.

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