Database Design

Good Practices

Single Source of Truth

When designing a database, it is important to have a single source of truth. This means that you should avoid duplicate data. For example, if you have a table of users, you should not have a table of users and a table of admins. Instead, you should have a table of users and a column in that table that specifies whether or not the user is an admin.

• Avoid duplicate data
• Avoid data that can be derived from other data

Tip

As designs grow more complex, it can be difficult to maintain a single source of truth. This is why it is important to have a good design from the start. Sometimes refactoring data into a reference table can be a good solution to this problem.

Entity Relationship Diagram

An easy way to visualize a database design is to use an entity relationship diagram (ERD). An ERD is a diagram that shows the relationships between entities in a database. An entity is a table in a database. A relationship is a connection between two entities.

Diagrams

Drawing diagrams using tools such as draw.io can be a good way to visualize your database design.

Full ERD Software

As your database design grows more complex, you may want to use a full ERD software. These tools allow you to create a database design and then generate the SQL code to create the database. They also have the ability to do analysis on your database design to find potential problems.

A good example of this is MySQL Workbench or ERD-Editor.