Search by

    Conceptual Design Process.

    created_at:June 18, 2022

    updated_at:June 18, 2022

    Today, I am going to explain conceptual design process.

    Purposes of this article creation

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

    What is Conceptual Design

    • An abstraction and representation of the information structure of the target world.
    • Simply expressed, it is like “data visualization”.

    【Three main levels of conceptual data models】

    • BEV(Bird’s-eye view) data model: a model of entities only.
    • Skeleton(Bones) data model: A model in which entities and major attributes are described.
    • Detailed data model: a model in which all data items are described.

    Conceptual Design Process

    In this case, I will create Conceptual Data Model for a SNS task management application on Cacoo as a deliverable by using “Top-Down Approach”.


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

    ※Originally, the process of “determining the type of database” is included in the first or third step of the top-down approach. In this case, I will use MySQL (RDBMS).

    ※Then, create a table definition that is converted into a form consistent with the database (Table Definition).

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

    1. Create ERD(Entity-Relationship Diagrams)

    • Create a rough ERD by identifying the entities (groups of data) necessary for the system to be created and considering their relationships and cardinality (relationship of numbers), etc.
    • In this case, we will describe the ERD using IE notation(Information Engineering notation).


    ※This is for my own use.

    2. Identify Attributes

    • For each entity, identify necessary attributes (data items).


    1. Identify Attributes
    2. Determine the primary key

    ※ Automatically assigns id as primary key in Ruby on Rails. ※Omit id and created_at, updated_at.


    ※This is for my own use. ※In this time, Omit “3. Normalization Process” because it has already been normalized.

    3. Normalization

    • Normalize the attributes identified (up to the 3NF).
    • If there is a many-to-many association in the ERD, make it one-to-many.

    【A little explanation of terms】

    • About normalization
      • Normalization:
      • 1NF(First normal form):A method of separating the repeated parts of a table that extend horizontally in unnormalized form, arranging them vertically, and eliminating horizontal overlaps.
      • 2NF: Separate duplicated data each time the details are repeated in the 1NF.
      • 3NF:Separate the parts that can be further separated (Example:Related entity(Linked entity)).


    ※This is for my own use.

    Deliverables(Conceptual Data Model)

    ※This is for public use.

    Notes on the creation of conceptual data models.

    • Supertype/Subtype
      • Coexisting subtype(Note.)
      • Exclusive subtype
    • Normalization
      • Relational schema should be normalized to 3NF.
    • Naming Rules
      • Entity type names should be appropriate names that give a concrete image of the entity(As much as possible, use the name as it is actually used.).
    • One-to-one and many-to-many relationships
      • Create a one-to-many relationship between entity types basically.
        • The primary key of the one entity type is set as the foreign key of the many entity type.
      • In a one-to-one relationship
        • Set the primary key of either entity type as the foreign key of the other entity type.
        • In the above, set a foreign key on the side where the instance will occur later, paying attention to the time(period) when the instance will occur basically.
      • In a many-to-many relationship
        • Since all attributes cannot be managed as they are, break them down into one-to-many relationships using connected(linked) entities.


    [Data Modeling]

    [Conceptual Design]

    ※The conceptual data model provides a BEV(bird’s eye view) of the entire business activity.

    [How to write ERD]



    Summary/What I learned this time

    In this study, I learned about conceptual design using a top-down approach. In order to be able to design various systems, I must learn design methods and gain experience in actually designing systems. I want to become an engineer who can think about systems from the design stage, not just write code.

    © 2022, rubicon44TechBlog All rights reserved.