Core Database Design Concepts

Introduction

A learning platform stores information about students, courses, enrollments, and payments. When these details are mixed together without a clear structure, repeated information and inconsistent updates can make the data difficult to manage.

Core database design concepts help us decide what to store, how records connect, and which rules the database should enforce.

Core Concepts of Database Design

Entities

An entity is a person, object, event, or concept about which we store information.

On a learning platform, a student, a course, and a payment can each be an entity. Identifying these entities helps clarify what each record represents.

Attributes

An attribute is a property that describes an entity.

For example, a student may have a student ID, name, and email address. A course may have a course code, title, and listed price.

In a relational database, these attributes are typically represented as table columns.

Relationships

A relationship describes how entities are connected.

A student enrolls in a course, and an instructor teaches a course. An enrollment record can capture the connection between a particular student and a particular course.

Foreign-key constraints help enforce valid references between related records.

Relationship Cardinality

Cardinality describes how many records can be associated through a relationship. The common types are:

Cardinality	Meaning
One-to-one	Each record can relate to at most one record on the other side.
One-to-many	One record can relate to many records, while each of those relates to at most one on the first side.
Many-to-many	Records on both sides can relate to multiple records on the other side.

For example, a student can enroll in several courses, and each course can have several students. This is a many-to-many relationship, commonly represented using an Enrollments table.

Database Constraints

Constraints are rules the database enforces to protect the consistency of stored data.

Constraint	Purpose
PRIMARY KEY	Gives each row a unique, non-NULL identifier.
FOREIGN KEY	Ensures that a referenced record exists.
UNIQUE	Enforces uniqueness for selected columns or combinations of columns.
NOT NULL	Prevents a required column from containing NULL, which represents missing or unknown information.
CHECK	Checks a condition, such as disallowing a negative payment amount.

These rules depend on the application’s requirements. For example, if each account must use a different email address, that requirement should be enforced with a uniqueness constraint.

Database Normalization

Normalization organizes relational tables to reduce unnecessary duplication and avoid problems when inserting, updating, or deleting data. These problems are called data anomalies.

For example, a course’s current title can be stored in the Courses table, while enrollment records refer to its course ID. Updating the title then requires changing one course record instead of many enrollment records.

Repeated identifiers still connect the tables; normalization reduces unnecessary repetition of the details they identify.

Why These Concepts Matter

Together, these concepts give records a clear purpose, keep related information connected, and help enforce business rules. They make it easier to avoid conflicting details, invalid references, and unwanted duplicates as the application changes.

Summary

Entities identify what a database represents, attributes describe it, and relationships connect records. Cardinality defines how many records can be related, constraints enforce declared rules, and normalization reduces unnecessary duplication. Together, these concepts provide a practical foundation for designing consistent, maintainable relational databases.