Three Types of Database Design: A Complete Guide for Beginners

Database design

Database Design is a systematic approach that establishes how information is kept, retrieved, and controlled. In order to create a strong and effective database structure,it needs three main stages: conceptual, logical, and physical design.

Conceptual database design

The first stage, known as conceptual database design, determines the key data components, their connections, and related limitations. It serves as the basis for the logical and physical design phases that follow by encapsulating the organization's business principles in a high-level abstract model.

In particular, entities and their properties are identified using conceptual database design. Entities, like Student, are representations of actual things or ideas, whereas attributes, like Name and Student_ID, explain their characteristics.

The following procedures are commonly employed to put conceptual database design into practice:

1. Gathering Requirements : To determine important data aspects, gather business requirements.
2. Identify Entities and Attributes: Identify the main entities and the attributes that are related to them.
3. Define Relationships: Explain the relationships between entities.
4. Make an entity-relationship diagram using entity-relationship modeling.
5. Review and Feedback: Consult stakeholders to validate the conceptual model and make necessary adjustments based on their suggestions.

Using Student, Teacher, and Department Entities as an Example

1. Requirements Gathering
   • Students enroll in departments.
   • Teachers belong to departments.
   • Teachers teach students.
2. Identify Entities and Attributes
   • Department_ID, Department_Name
   • Student_ID, Name, Email
   • Teacher_ID, Name, Email
3. Define Relationships
   • Department has many Students → one-to-many.
   • Department has many Teachers → one-to-many.
   • Teachers teach Students → many-to-many.
4. Entity-Relationship Modeling
5. Review and Feedback
   • Finally, we discussed the final ER diagram with stakeholders to ensure it accurately reflects requirements and revised it as needed.

Logical database design

Logical database design is the process of creating a comprehensive data model that accurately represents an organization's data structure.

The following are the primary tasks involved in logical database design.

1. Making a logical model out of the conceptual ER diagram.
2. Establishing Primary and Foreign Keys are defined to enforce relationships between tables, and primary keys are assigned to uniquely identify records.
3. To remove redundancy, apply normalization and arrange data into well-structured tables.
4. Defining Data Types and Restrictions Decide on suitable data types.
5. Business rules should be documented and converted into database constraints.
6. Prior to physical implementation, review and validate the logical schema with the appropriate person to make sure it satisfies criteria.

Example:

1. Mapping the Conceptual Model
   • Student(Student_ID, Name, Email, Department_ID)
   • Department(Department_ID, Department_Name)
   • Teacher(Teacher_ID, Name, Email, Department_ID)
   • Teaching(Teacher_ID, Student_ID) → to represent many-to-many relationship
2. Defining Primary and Foreign Keys
   • Department_ID → primary key in Department
   • Student_ID → primary key in Student
   • Teacher_ID → primary key in Teacher
   • Teaching uses a composite primary key: (Teacher_ID, Student_ID)
   Foreign Keys:
   • Student.Department_ID → Department.Department_ID
   • Teacher.Department_ID → Department.Department_ID
   • Teaching.Teacher_ID → Teacher.Teacher_ID
   • Teaching.Student_ID → Student.Student_ID
3. Applying Normalization:Normalize all tables up to 3NF
4. Specifying Data Types and Constraints
   For Student:
   • Student_ID INT PRIMARY KEY
   • Name VARCHAR(100) NOT NULL
   • Email VARCHAR(100) UNIQUE
   • Department_ID INT NOT NULL
   For Department:
   • Department_ID INT PRIMARY KEY
   • Department_Name VARCHAR(100) NOT NULL
   For Teacher:
   • Teacher_ID INT PRIMARY KEY
   • Teacher_Name VARCHAR(100) NOT NULL
   • Email VARCHAR(100) UNIQUE
   • Department_ID INT NOT NULL
5. Documenting Business Rules:
   • A student must belong to a department.
   • A teacher must belong to a department.
   • A student can be taught by multiple teachers, and vice versa.
6. Examining and Verifying
   • Give the necessary individual access to SQL definitions so they can verify the structure prior to implementation.

Note: A teaching table, also known as a junction table, links the primary keys of two related tables to simulate a many-to-many relationship.

Physical database design

Converting the logical data model into tangible structures that are tailored for a particular database management system is known as physical database design. It emphasizes performance, dependability, and scalability in the way data is stored, indexed, and accessed.

Important tasks for designing a physical database

• Table Structure Definition: assign suitable data types and provide column-level definitions to convert logical models into physical tables.
• Building Indexes: create indexes to improve query performance by accelerating the retrieval of data.
• Defining Constraints: Set rules like PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK to maintain data integrity.

Example:

1. Define Table Structures with Data Types and Constraints:

 
CREATE TABLE Department ( 
Department_ID INT PRIMARY KEY, Department_Name VARCHAR(100) NOT NULL
 );
CREATE TABLE Teacher (
Teacher_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Department_ID INT,
FOREIGN KEY (Department_ID) REFERENCES Department(Department_ID)
);
CREATE TABLE Student (
Student_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
Department_ID INT,
FOREIGN KEY (Department_ID) REFERENCES Department(Department_ID)
);

2. Implement a Many-to-Many Relationship Using a Junction Table:

  
 CREATE TABLE Teaching ( 
 Teacher_ID INT, Student_ID INT,
 PRIMARY KEY (Teacher_ID, Student_ID),
 FOREIGN KEY (Teacher_ID) REFERENCES Teacher(Teacher_ID), 
 FOREIGN KEY (Student_ID) REFERENCES Student(Student_ID) 
 );
 
 

3. Indexing for Performance:

  
 CREATE INDEX idx_student_email 
 ON Student(Email);
 CREATE INDEX idx_teacher_email ON Teacher(Email);
 CREATE INDEX idx_teaching_teacher 
 ON Teaching(Teacher_ID); 
 CREATE INDEX idx_teaching_student ON Teaching(Student_ID);