As backend developers, one important skill we eventually have to learn is how to convert product requirements into structured databases.
When learning databases, most tutorials teach tables, primary keys, foreign keys, and relationships. However, when I started working on real projects, I realized something:
Nobody gives you tables.
They give you a Product Requirements Document (PRD).
Recently, I ran into this problem while working on an online school project.
I received a PRD describing students, teachers, sessions, terms, enrollments, subscriptions, classes, video lessons, and payments.
My immediate question was:
"How exactly am I supposed to convert all of this into database tables?"
At first, the document looked overwhelming.
Eventually, I realized database design is simply translating business requirements into structured data.
This article explains the process I used.
The PRD I Received
Imagine receiving requirements similar to this:
- Students can register accounts
- Teachers can create classes
- Students can enroll in classes
- Classes belong to academic sessions and terms
- Teachers can upload video lessons
- Students can subscribe to paid plans
- Students can attend live classes
- Parents can monitor student performance
Looking at this initially felt chaotic.
So I broke it down step by step.
Step 1: Extract Entities (Find The Important Things)
The first thing I did was stop thinking about databases entirely.
Instead, I asked:
"What are the important things this system talks about?"
Reading through the requirements, I highlighted major nouns.
From the PRD I extracted:
- Student
- Teacher
- Class
- Enrollment
- Session
- Term
- Subscription
- Video Lesson
- Parent
- Payment
These become possible entities.
Think about entities as:
Things the system needs to store information about.
At this stage, I did not worry about columns or relationships.
I simply tried identifying the major building blocks.
Step 2: Define Attributes (What Information Should We Store?)
After identifying entities, I asked:
"What information does the system need about each entity?"
For Students:
Student Table
- id
- full_name
- date_of_birth
- created_at
Teacher Table
- id
- full_name
- specialization
Class Table
- id
- title
- teacher_id
- session_id
- term_id
Subscription Table
- id
- student_id
- subscription_plan
- start_date
- expiry_date
At this point, I started seeing the database take shape.
A rule that helped me:
If users need to view, update, search, or filter information, it probably belongs in the database.
Step 3: Map Relationships (How Does Everything Connect?)
This was where things became clearer.
I started asking:
"How do these entities interact?"
One-to-One Relationship (1:1)
One student account may have one profile.
Student
1 → 1 Profile
One-to-Many Relationship (1:N)
One teacher teaches multiple classes.
Teacher
1 → Many Classes
This means:
Class table contains:
teacher_id
because many classes belong to one teacher.
Another example:
One session contains multiple terms.
Session
1 → Many Terms
Many-to-Many Relationship (N:N)
Students can enroll in multiple classes.
Classes contain multiple students.
This creates:
Student ↔ Enrollment ↔ Class
This means Enrollment becomes its own table.
Enrollment Table:
- student_id
- class_id
- enrolled_at
This was one of the biggest mindset shifts for me.
I realized:
Relationships often create new tables.
Step 4: Draw The Database Before Writing Code
Before creating tables, I drew an Entity Relationship Diagram (ERD).
A simplified version looked like this:
Drawing the relationships exposed mistakes immediately.
I discovered missing connections and unnecessary tables before writing code.
This saved a lot of refactoring later.
Step 5: Normalize The Database
At first, I made a mistake.
I considered storing session names and term names directly inside classes.
Something like:
Class Table
- title
- teacher_name
- session_name
- term_name
This quickly becomes problematic.
What happens when:
"2026/2027 Academic Session"
exists in 500 rows?
Updating becomes painful.
Instead:
Session Table
- id
- name
Term Table
- id
- session_id
- name
Class Table
- session_id
- term_id
Now information exists only once.
This reduces duplication and improves consistency.
A simple rule I follow:
If the same information appears repeatedly, it may belong in another table.
Step 6: Test Unhappy Flows
This was the step I almost ignored.
Most people design databases for happy scenarios.
Real systems fail in weird ways.
I started asking questions like:
"What happens if a teacher leaves?"
"What happens if a session ends?"
"What happens if a student subscription expires?"
"What happens if somebody deletes a class with enrolled students?"
These questions forced me to think about constraints.
Examples:
Should deleting a class:
- Delete enrollments automatically?
OR
- Prevent deletion until enrollments are removed?
Should deleting a session:
- Delete terms?
OR
- Archive them?
These decisions affect foreign key rules and database behavior.
The more edge cases I explored, the better the schema became.
What I Learned
Initially, database design felt difficult because I thought I was designing tables.
I eventually realized:
I was actually designing representations of business rules.
Today, whenever I receive a PRD, I follow this process:
- Extract entities
- Define attributes
- Map relationships
- Draw the ERD
- Normalize
- Test edge cases
Database design stopped feeling like guessing.
It became a process.
And honestly, most good database design is simply understanding the product deeply enough.
This process is not perfect, I still discover missing relationships or edge cases after designing schemas.
But having a repeatable process makes database design feel far less intimidating.
If you are learning backend development, I strongly recommend taking random products around you and asking:
"If I received this PRD today, how would I design the database?"
Top comments (0)