Database Design, Schema and Normalization Explained With Examples

Database design is the process of defining how data is stored, organised and related within a database system. It maps real-world entities into tables, assigns keys and constraints, and eliminates redundancy through normalisation. Good database design prevents data corruption, speeds up queries and makes applications far easier to maintain as they scale.

• Key Takeaways
• Database design moves through two stages: logical (what data and how it relates) and physical (how it is stored on disk).
• A schema defines the structure of your database, including tables, columns, data types and relationships.
• Primary, foreign, candidate and composite keys enforce uniqueness and link tables correctly.
• Normalisation (1NF, 2NF, 3NF) removes redundancy and prevents insertion, update and deletion anomalies.
• Denormalisation is sometimes the right call when read performance outweighs the cost of redundancy.

How Good Database Design Starts

Every database design project starts with a problem statement. For this article, that problem is a college course-enrolment system. Students enrol in courses, courses are taught by faculty, and we need to track grades. Simple enough on paper, chaotic in a spreadsheet.

Logical Design vs Physical Design

Logical design answers the question: what data do we need and how does it relate? You are not thinking about storage engines, indexes or partitions yet. You are drawing an ER diagram (Entity-Relationship diagram) that maps entities like Student, Course and Enrolment, defines their attributes and shows the relationships between them.

Physical design comes later. It translates the logical model into actual table definitions, chooses data types, sets up indexes and decides how rows are stored on disk. Mixing the two stages is one of the most common beginner mistakes, and it leads to schema decisions driven by implementation quirks rather than business logic.

From ER Diagram to Tables

In our college system, the ER diagram gives us three core entities: Student, Course and Faculty. The relationship between Student and Course is many-to-many, so we resolve it with a junction table called Enrolment. Each entity becomes a table, each attribute becomes a column, and each relationship becomes a foreign key.

That mapping process is the heart of what is database design in practice. Getting it right before writing a single line of SQL saves enormous rework later. According to the 2023 Stack Overflow Developer Survey, SQL remains the most commonly used language among professional developers worldwide, with over 51% of respondents using it regularly — a figure that underlines why understanding the design beneath the SQL matters for anyone building a career in data or software engineering.

Schemas, Keys and Constraints in Database Design

Once the ER diagram is solid, you write the schema. A database schema in DBMS is the formal blueprint that describes every table, column, data type, key and constraint. Think of it as the architectural drawing before construction begins.

How to Write a Database Schema

Writing a schema means specifying, for every table, which columns exist, what data type each column holds, which column is the primary key, which columns reference other tables as foreign keys, and what constraints apply. For our enrolment system, the Student table looks like this:

Column	Data Type	Constraint
student_id	INT	PRIMARY KEY, NOT NULL
full_name	VARCHAR(100)	NOT NULL
email	VARCHAR(150)	UNIQUE, NOT NULL
date_of_birth	DATE	CHECK (date_of_birth < CURRENT_DATE)
department_id	INT	FOREIGN KEY references Department(dept_id)

The schema above is a working answer to how to write a database schema. You name columns, assign types and attach constraints. The discipline is in the decisions, not the syntax.

Primary, Foreign, Candidate and Composite Keys

A primary key uniquely identifies every row in a table. student_id does that job in the Student table. A foreign key links one table to another and enforces referential integrity, meaning you cannot enrol a student who does not exist in the Student table.

A candidate key is any column or combination that could serve as a primary key. In our Student table, both student_id and email are candidate keys. We pick one as the primary key and mark the other UNIQUE.

A composite key is a primary key made up of two or more columns. In the Enrolment table, neither student_id nor course_id alone is unique. The combination (student_id, course_id) is unique, so that pair becomes the composite key. This is the most practical answer to what is composite key in database: it is a multi-column primary key used whenever no single column can guarantee uniqueness.

What Are Constraints in a Database?

Constraints are rules the database engine enforces automatically. They are your last line of defence against bad data. The main ones are NOT NULL, UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY.

India’s technology sector processes enormous transaction volumes daily. A 2022 NASSCOM report estimated that India’s data management services market would grow at 18% CAGR through 2025, driven by banking, fintech and e-commerce. Companies like Infosys, TCS and Razorpay rely on tightly constrained schemas to prevent financial loss at scale. If you are exploring AI, blockchain and data science careers in India, solid schema design is a baseline skill every hiring manager expects.

Database Normalisation from 1NF to 3NF

Normalisation is the process of restructuring a database schema to reduce data redundancy and improve data integrity. The question what is normalisation in database comes up in almost every data engineering or backend interview in India, so here is a walkthrough using our enrolment example.

The Unnormalised Starting Point

Imagine a single flat table called Enrolment_Raw with columns: student_id, student_name, email, course_id, course_name, faculty_name, grade, dept_name. This table stores everything in one place. It looks convenient. It is a maintenance disaster.

First Normal Form (1NF)

1NF requires that every column holds atomic (indivisible) values and that there are no repeating groups. If a student enrols in three courses and all three course IDs are stored in a single comma-separated cell, that violates 1NF. The fix is to give each enrolment its own row. After applying 1NF, the composite key (student_id, course_id) identifies each row uniquely.

Second Normal Form (2NF)

2NF applies only to tables with composite keys. It requires that every non-key column depends on the whole primary key, not just part of it. In our 1NF table, student_name depends only on student_id, not on course_id. That partial dependency causes update anomalies.

The fix is to split the table. Student attributes go into a Student table keyed on student_id. Course attributes go into a Course table keyed on course_id. The Enrolment table keeps only student_id, course_id and grade.

Third Normal Form (3NF)

3NF removes transitive dependencies, where a non-key column depends on another non-key column rather than directly on the primary key. If faculty_name determines faculty_email and faculty_department, those attributes depend on the faculty, not on the course. The fix is a Faculty table with faculty_id as the primary key, and a faculty_id foreign key in the Course table.

A 2021 study published in the International Journal of Database Management Systems (IJDBMS, Vol. 13, No. 4) found that schemas normalised to 3NF showed up to 40% fewer data anomalies in production compared to unnormalised flat-file equivalents across a sample of enterprise applications. The trade-off is more joins at query time, which brings us to denormalisation.

When Denormalisation Makes Sense

Denormalisation intentionally introduces some redundancy to speed up read-heavy workloads. If your analytics dashboard runs the same expensive five-table join millions of times a day, pre-joining those tables into a summary table is a legitimate engineering choice. The key word is intentional. You denormalise after profiling, not as a shortcut to avoid thinking about database design. For a deeper look at analytical data structures, the Big Data Analytics notes on 3University cover dimensional modelling and data warehousing in detail.

A data dictionary sits alongside your schema and documents what every table and column means in business terms. Every production database should have one.

If you are building these skills while working full-time, the bootcamp training programs at 3University are designed for working professionals who need practical, project-based experience. For those thinking about a bigger pivot, knowing how to design a clean schema is a prerequisite for feature engineering. The guide on how to shift from data science to AI and ML explains exactly where database skills fit into that transition.

The REACH learner community at 3University is worth joining if you want peer feedback on your schema designs. Real critique from practitioners beats reading alone every time.

Frequently Asked Questions

What is database design?

Database design is the process of defining the structure, relationships and constraints of a database so it stores data accurately, avoids redundancy and supports the application’s queries efficiently. It starts with a logical model (ER diagram) and moves to a physical schema with tables, keys and constraints before any data is loaded.

What is a database schema in DBMS?

A database schema is the formal definition of a database’s structure. It specifies every table, column name, data type, primary key, foreign key and constraint. The schema is the blueprint the database management system uses to validate every insert, update and delete automatically on every transaction.

What is normalisation in a database?

Normalisation is the step-by-step process of reorganising a database schema to eliminate redundant data and prevent anomalies. Each normal form targets a specific dependency problem: 1NF enforces atomic values, 2NF removes partial dependencies, and 3NF removes transitive dependencies. The result is a schema where every fact is stored exactly once.

What is a composite key in a database?

A composite key is a primary key made up of two or more columns that together uniquely identify a row. It is used when no single column is unique enough on its own. In a course-enrolment table, neither student_id nor course_id alone is unique, but the combination of both is, so the pair forms the composite key.

How do you write a database schema?

Start by listing your entities from the ER diagram, then define a table for each. For every table, specify column names, data types, the primary key, any foreign keys referencing other tables, and constraints such as NOT NULL, UNIQUE and CHECK. Document the schema in a data dictionary so the business meaning of each column is clear to every future developer.

Why is database design important for data science and AI roles in India?

Clean database design is a prerequisite for reliable feature engineering, model training pipelines and real-time inference systems. Indian tech employers at companies like Infosys, Wipro and fintech startups consistently list DBMS and schema design as required skills in data science and AI job descriptions, making it a foundational competency for career growth in these fields.

The next step after reading is doing. Pick a simple real-world problem, sketch the ER diagram by hand, write the schema for each table, normalise it to 3NF and identify every key and constraint. That one exercise will cement these concepts faster than re-reading any article.

If you want a structured path with mentors and real projects, explore the online certification courses at 3University. The programmes cover Cybersecurity, Ethical Hacking, Artificial Intelligence, Blockchain and Web3, built around hands-on labs and real-world projects so you graduate with a portfolio, not just a certificate.

Last updated: August 2026. Reviewed by the 3University editorial team.