Search by

    Database Design and Implementation(The Big Picture).

    created_at:June 11, 2022

    updated_at:June 11, 2022

    Today, I am going to explain how to do Database Design and Implementation.

    Purposes of this article creation

    • To be able to create DB tables in MySQL in Ruby on Rails on my own.

    Way of thinking

    • One of the causes of slow service speed is “heavy at the DB level (SLOW QUERY or N+1 queries are occurring)
    • The role of DB is “to store data in a form that is easy to retrieve data that the service wants”.
      • What is a table/column that is easy to retrieve?
        • Different for each services?
    • Initial design is important, if you want to fix it later, there are useful gems (I think Studyplus introduced them.)

    ※But, speed and quality are always a trade-off.


    Database Design Flow

    1. Conceptual Design
    2. Logic Design
    3. Physical Design

    Conceptual Design

    • What?
      • Designing a conceptual data model to bring the whole thing together and provide a complete view of the system.
    • Deliverable
      • Conceptual Data Model(→Define relational schema.)


    • Top-Down Approach:A method in which the entire database is considered first, and then it is progressively subdivided into smaller and smaller pieces.
        1. Create ERD(Entity-Relationship Diagrams)
        1. Identify Attributes
        1. Normalization

    →When new data is added, that is to say, new development, this method may be used more often.

    • Bottom-Up Approach:A method of identifying the actual data and gradually integrating it.
        1. Identify Attributes
        1. Normalization
        1. Create ERD(Entity-Relationship Diagrams)

    Logic Design

    • What?
      • Designing to relate databases to users and non-database systems.
    • Deliverable
      • Logic Data Model(→Define table structure.)
        • Table Structure
        • Configure instances (data)
        • Constraints


    • Create CRUD Matrix(CRUD Analysis)
    • Create Decision tables
    • Code Design
      • Primary Key
      • External key
    • Think about Data Migration(Data Transition)
    • Do Security Design

    Physical Design

    • What?
      • Design to physically optimize the data model according to each DBMS.
    • Deliverable
      • Physical Data Model
        • Understanding of DBMS specifications
        • Table Definition
        • Calculation of data requirements
      • Implement Database(To implement a database, it is necessary to describe data types, constraints, and so on according to each DBMS.)


    • Reliability Design(to ensure reliability)
    • Performance Design(to improve performance)
    • Operation and Maintenance Design(to enable stable operation)

    What to do about Rails and MySQL(Especially in Physical Design)

    • Constraints
      • DB
      • Model
    • index
    • Addressing the N + 1 Problem
    • How to issue SQL (using Active Record)

    ※In Rails, tables are created using migration files. ※Migration file is a file that manipulates DB via active record (It is important to understand Active Record.).

    About Security

    • User Authentication
    • Role-based Access Control
    • Encryption
    • Log data Management
    • Use of Digital evidence
      • Time synchronization (NTP)
      • SIEM
      • WORM
      • Blockchain


    Summary/What I learned this time

    Subsequent articles will discuss conceptual design, logical design, and physical design in more detail.

    © 2022, rubicon44TechBlog All rights reserved.