How to Design Database - Designing a Database Schema

ERD|Software Academy| 26 May 2020 | Dusan Rodina - softwareideas.net
If you want to design a data model for your database, you should use an entity-relationship diagram (also known by ERD acronym). It may be used for conceptual, logical, and also physical data models. Alternatively, you may also use a UML class diagram for the same purpose.

How to Create a Data Model?

The best way to create a data model is to use an ER diagram. Begin with a conceptual model, add more detail to create a logical model. Build your model up to the physical model that allows you to define the detailed database schema design.

Data Model Types

There are three main types of data models which provide a different level of details:

  • Conceptual
  • Logical
  • Physical

Conceptual Data Model

If you start to design a new database model, you should start with a conceptual model. At first, identify the main entities representing the domain objects. Then add supporting entities that are needed by the modeled system. Discover attributes that describe the entities. The important part of the models is also relationships. Analyze what is the connection between particular entities, how they are related. Connect the related entities with the relationship connectors.

When finalizing your conceptual model, check whether all entities need to be stored in a database or they may be omitted. You may use this checklist:

  • Does the entity exist only for a temporary time within a local memory? If yes, we do not need it in our database model.
  • Can the entity be derived from other stored data? If yes, we do not need it either.
  • s the entity unnecessary now but it may be useful later? If so, we do not include it now and we will revise the model in the future.

The conceptual data model includes the following parts:

  • Entities
  • Untyped attributes
  • Relationships

Logical Data Model

It is a continuation and refinement of the conceptual data model. The logical data model is independent of a specific database or storage system. It represents a model of a domain and its objects. It brings more details compared to the conceptual model, the attributes are typed, but the types do not need to be the exactly specified (the model may specify that the attribute is number or integral number but there is no need for the exact physical data format).

The logical data model includes these parts:

  • Entities
  • Typed attributes
  • Relationships

Physical Data Model

The physical data model is based on the logical model with even more refinements and platform-specific details. The primary and foreign keys must be specified on this modeling level. All the attributes must be typed with target database types with specified nullability. The indexes should be also mentioned.

The physical data model includes these parts:

  • Entities
  • Association entities
  • Typed attributes (types of the target database must be used)
  • Relationships
  • Primary keys
  • Foreign keys
  • Indexes

How to Design a Good Database Model?

A good database design should meet the business requirements. All the data should be stored correctly without the loss of any needed information. The good data model should prevent data duplication and redundancy. The relationships should be correctly defined and the model should be divided into meaningful entities (tables). The naming should be comprehensible and consistent. The well-defined database should ensure data integrity. The orphan data or missing reference data should be avoided.

Database Normalization

The database normalization is an important part of database design. Its goal is to remove or reduce data redundancy and improve the data integrity of the entity-relationship model.

  • Unnormalized form - UNF
  • First normal form - 1NF
  • Second normal form - 2NF
  • Third normal form - 3NF
  • Elementary key normal form - EKNF
  • Boyce–Codd normal form - BCNF
  • Fourth normal form - 4NF
  • Essential tuple normal form - ETNF
  • Fifth normal form - 5NF
  • Domain-key normal form - DKNF
  • Sixth normal form - 6NF

New Comment

Comment