Skip to content
Internet of Things - Knowledgebase

Database Design

In lots of applications, you need to store data. This data can be anything from user information to the content of a blog post. In order to store this data, you need a database. A database is a collection of data that is stored in a structured way. One of the most popular databases is MySQL. MySQL is a relational database management system. This means that the data is stored in tables and the tables are related to each other. This page will show you how to create an ERD (Entity Relationship Diagram) from user needs, how to create a database schema from an ERD, and how to create a database from a database schema. Make sure to follow our conventions to create a consistent database design.

Entity Relationship Diagram (ERD) from User Needs

An Entity Relationship Diagram (ERD) is a visual representation of the data that is needed to be stored in a database. It shows the entities (tables) and the relationships between them. To create an ERD, you need to understand the user needs. For example, if you are creating a database for a recipe website, you need to understand what data needs to be stored. This could be the recipe name, the ingredients, the instructions, and the user who created the recipe. Once you understand the user needs, you can create an ERD.

Here is an example of an recipe website, where we look at one of the recipes:

Recipe website

Figure 1: Recipe website, source: https://www.ah.nl/allerhande/recept/R-R1189426/pannenkoeken-basisrecept

Sometimes the user needs can be a website, a document, or a conversation with the user. In this case, we can see that the user needs are a website. From this website, we can see that the data that needs to be stored, let's create an ERD.

Start by creating a list of entities (tables) that need to be stored in the database. In this case, we have the following entities:

  • Recipe
  • Ingredient
  • Instruction
  • Category

Using the tool draw.io, you can create an ERD. Make sure to select the Entity Relation > Entitiy-diagram template.

Recipe website ERD

Figure 2: Start with entities of your ERD.

Next step is to add the attributes to the entities. Make sure you are getting all the attributes from the user needs. Don't go too far in the details; this is still only an ERD. But make sure you have all the attributes that are needed.

Recipe website ERD attributes

Figure 3: Fill in the attributes of your ERD.

There is a different in attributes and columns; attributes are the properties of the entity without the data type. In this step it is also not important to adjust the attributes into smaller parts, like first name and last name. This will be done when creating the database schema.

Now we are giving every entity an Identifier. This is an existing attribute that is unique for every entity. In this case, we can use the recipe name as an identifier for the recipe entity. The ingredient name can be used as an identifier for the ingredient entity. The instruction number can be used as an identifier for the instruction entity. And the category name can be used as an identifier for the category entity.

Identifiers are marked with an underline in the ERD.

Recipe website ERD attributes identifiers

Figure 4: Add identifiers to your ERD.

The last step is to add the relationships between the entities. In this case, we have the following relationships:

  • A recipe is cooked with multiple ingredients
  • A recipe reads multiple instructions
  • A recipe is part of one or more categories
  • An ingredient can be used in multiple recipes
  • An instruction describes part of a recipe
  • A category has multiple recipes

Make sure you use the correct cardinality for the relationships.

Recipe website ERD relationships

Figure 5: Our full ERD.

Database Schema from ERD

Now that we have created an ERD, we can create a database schema. A database schema is a blueprint of the database. It shows the tables, the columns, and the relationships between the tables. To create a database schema from an ERD, you need to convert the entities, attributes, and relationships into tables, columns, and foreign keys.

You have to use MySQL Workbench to create a database schema. MySQL Workbench is a visual tool that allows you to design databases, create tables, and manage data. You can download MySQL Workbench from the MySQL website.

Follow these steps to create a database schema from an ERD:

  1. Create a table for each entity in the ERD. The table name should be the same as the entity name.
  2. Create a table for every many-to-many relationship in the ERD. The table name should be the names of the two entities that are related.
  3. Create a column for each attribute in the ERD. The column name should be the same as the attribute name.
    • Make sure to adjust the attributes into smaller parts, (like first name and last name instead of only name) when needed.
    • Select the correct data type for each column. For example, use VARCHAR for strings, INT for integers, and DATE for dates.
  4. Create a primary key for each table. The primary key is most of the time be the identifier of the entity.
    • When the identifier is not unique, you can create a new column with a unique identifier (surrogate key).
    • If up to 3 columns are needed to create a unique identifier, you can use a composite key.
  5. Create a foreign key for each relationship in the ERD. The foreign key should be the name of the related column, if the name is ambiguous, prefix with the name of the table.

Make sure to give your schema a name and save it in a file, there is always a chance that you need to adjust the schema later.

Database from Database Schema

Now that we have created a database schema, we can create a database. To create a database from a database schema, you need to execute the SQL code that is generated by MySQL Workbench. The SQL code will create the tables, columns, and relationships in the database.

Be warned, this is a destructive operation. If you already have a database with the same name, it will be overwritten. Make sure to backup your database before executing the SQL code.

Follow these steps to create a database from a database schema:

  1. Open MySQL Workbench and make sure you have saved the credentials of the database you want to use.
  2. Open the database schema file in MySQL Workbench.
  3. Click on the Database menu and select Forward Engineer....
  4. Select the database connection you want to use and click Next.
  5. Select the following options:
    • Gemerate INSERT statements for tables
    • DROP objects before each CREATE object (be careful with this option, it will delete all data in the database)
  6. Click Next.
  7. Now you can review the SQL code that will be executed. Make sure to review the code and click Execute to create the database.
  8. From step 7 you can also save the SQL code to a file, so you can execute it later.

You can now use PHPMyAdmin, or MySQL Workbench, to view the database of your application.

Conventions

We have our own conventions for the ERD and database schema. Make sure to follow these conventions to create a consistent database design. The language of the database is English.

ERD

  • Entitiy names
    • Singular
    • Capitalized
    • CamelCase
  • Attributes
    • Singular
    • Lowercase
    • camelCase
  • Relationships
    • Use the correct cardinality
    • Use the correct verb

Database Schema

  • Scheme
    • Singular
  • Table names
    • Singular
    • Capitalized
    • camelCase
  • Column names
    • Singular
    • Lowercase
    • snake_case
  • Relationships
    • Use the correct cardinality
    • Use the correct verb
  • Foreign keys
    • Optional if column name is ambiguous: prefix with table name
    • name of the related column
  • Primary Keys
    • The Primary Key should always be in the first column(s) of the table.

SQL

  • Reserved words
    • Uppercase
  • Names of schemes, tables, columns, ...
    • Use backticks (`) to prevent conflicts with reserved words
  • Aliases
    • Use if the name is not suitable for the query.
    • Use the syntax AS alias_name.
  • Tablename dot columnname
    • Use the syntax tablename.columnname to prevent conflicts with column names and to make the query more readable.

Always make sure you use the correct pronunciations in the ERD and database schema.

  • ERD:
    • Entity
    • Attribute
    • Identifier (one or more attributes, underlined)
  • Database schema:
    • Scheme
    • Table
    • Column
    • Primary key
    • Foreign key