Database Design and Implementation(The Big Picture).

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.

Explanation

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

【Methods】

• 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)
  • 2. Identify Attributes
  • 3. 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
  • 2. Normalization
  • 3. 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

【Methods】

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

【Methods】

• 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

References

• 「徹底攻略 データベーススペシャリスト教科書 令和3年度」：https://www.amazon.co.jp/%E5%85%A8%E6%96%87PDF%E3%83%BB%E5%8D%98%E8%AA%9E%E5%B8%B3%E3%82%A2%E3%83%97%E3%83%AA%E4%BB%98-%E5%BE%B9%E5%BA%95%E6%94%BB%E7%95%A5-%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%82%B9%E3%83%9A%E3%82%B7%E3%83%A3%E3%83%AA%E3%82%B9%E3%83%88%E6%95%99%E7%A7%91%E6%9B%B8-%E4%BB%A4%E5%92%8C3%E5%B9%B4%E5%BA%A6/dp/4295009903/ref=tmm_pap_swatch_0?_encoding=UTF8&qid=&sr=

Summary/What I learned this time

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