Build LMS from scratch. Part 3 — Define database entities.

In this part, I’ll prototype the core SQL database schema for a Learning Management System (LMS). Today’s goal is to define all key entities for both instructors and students, ensuring that our platform can efficiently grow in the future.

For simplicity, I’ll organize the tables in the following format:

  • Table Name — A brief description of its purpose.

  • Purpose — The main goal of the table

  • Fields — List of the fields, with their SQL types and descriptions

Users

Purpose: Stores main account information for both instructors and students.

Fields:

  • id primary key, uuid (Unique identifier)

  • first_name varchar (User’s first name)

  • last_name varchar (User’s last name)

  • emailvarchar (User’s email)

  • password_hashvarchar (Hashed password of the user)

  • created_at timestamp (Timestamp when the user completed registration in the system)

  • preferred_currency char, ISO currency code (Currency of the price in ISO currency code, like: USD, EUR. It’s needed to convert the money from different assets into one currency.)

Users roles

Purpose: Relationship between users and roles.

Fields:

  • id primary key, uuid (Unique identifier)

  • user_id foreign key to users.id (Links to a user in a system)

  • role enum: [‘instructor’, ‘student’] (User’s role in a system. One user can have a student and instructor role simultaneously)

Instructor projects

Purpose: Organizes courses under instructor-managed projects.

Fields:

  • id primary key, uuid (Unique identifier)

  • instructor_id foreign key to users.id (Links to a user with instructor role)

  • name varchar (Name of the project. One project can include different courses, for example, you want to manage 5 courses related to one topic in one project)

  • created_at timestamp (When the project was created)

Courses

Purpose: Represents courses created by instructors.

Fields:

  • id primary key, uuid (Unique identifier)

  • instructor_id foreign key to users.id (Links to a user with instructor role)

  • project_id foreign key to instructor_projects.id (Links to instructor project)

  • title varchar (Title of the course)

  • description text (Description of the course)

  • status enum: [‘draft’, ‘published’, ‘archived’] (A course can have different statuses throughout its lifecycle. It starts in the draft so instructors can work on the content without making it available to students. When it’s ready, the course moves to published, making it accessible via a public link. If it’s no longer needed, it can be archived — this stops new enrollments but keeps the course available for existing users)

  • price decimal (Price of the course agnostic to the currency)

  • currency char, ISO currency code (Currency of the price in ISO currency code, like: USD, EUR)

  • public_link varchar (Unique public link that you can use for course sharing)

  • created_at timestamp (When the course was created)

  • updated_at timestamp (When the course was updated last time)

Course modules

Purpose: Manages materials (videos, texts) that are grouped by module within a course.

Fields:

  • id primary key, uuid (Unique identifier)

  • course_id foreign key to courses.id (Links to a course entity)

  • title varchar (Title of the course)

  • description text (A brief description of what the module covers)

  • position int (Position/order of the module in the course. It will be needed for the UI positioning)

  • content jsonb (Content materials and metadata)

6. Enrollments

Purpose: Tracks which students are enrolled in which courses.

Fields:

  • id primary key, uuid (Unique identifier)

  • student_id foreign key to users.id (Links to a user entity with a student role)

  • course_id foreign key to courses.id (Links to a course entity)

  • created_at timestamp (Timestamp when the user was enrolled in a project)

Payment integrations

Purpose: Manages third-party payment integrations for instructors (e.g., mono, PayPal).

Fields:

  • id primary key, uuid (Unique identifier)

  • instructor_id foreign key to users.id (Links to a user with instructor role)

  • service_name varchar (Service name)

  • config jsonb (Configuration for the service provided by the user. May vary between different integrations)

  • created_at timestamp (Timestamp of when this record was created)

Student Payments

Purpose: Tracks the payments made by students for their courses.

Fields:

  • id primary key, uuid (Unique identifier)

  • payment_integration_id foreign key to payment integration (Links to payment integration created by instructor)

  • student_id foreign key to users.id (Links to a user with a student role)

  • course_id foreign key to courses.id (Links to a course entity)

  • amount decimal (Payment amount agnostic to the currency)

  • currency char, ISO currency code (Currency of the price in ISO currency code, like: USD, EUR)

  • payment_status enum: [‘pending’, ‘completed’, ‘failed’] (Initially, payment will have a pending status, indicating that the transaction is in process. Once the payment is completed, the status will change to completed, confirming that the payment has been successfully processed. If something goes wrong during the payment process, the status will be marked as failed, indicating that the transaction was unsuccessful)

  • created_at timestamp (Timestamp when payment was created by student — not paid)

  • paid_at timestamp (Timestamp when payment was completed)

9. Instructor transactions

Purpose: Tracks instructor earnings. For the MVP, we’ll only need a withdrawal functionality, allowing instructors to request payouts from their balance.

Fields:

  • id primary key, uuid (Unique identifier)

  • instructor_id foreign key to users.id (Links to a user with instructor role)

  • amount decimal (Payment amount agnostic to the currency)

  • currency char, ISO currency code (Currency of the price in ISO currency code, like: USD, EUR)

  • type enum: [‘withdrawal’] (Type of the operation, for now — only withdrawal)

  • status enum: [‘pending’, ‘paid’] (Operation status)

  • recipient_data jsonb (Data related to payment destination)

  • created_at timestamp (Timestamp when payment was created — not paid)

  • paid_at timestamp (Timestamp when payment was completed)

Conclusion

I ended up with 9 tables, which was not much as I expected. They feel well-structured and should cover our MVP version. Of course, as I build the app, I might find that something is missing and tweak it depending on the situation.

If you have an opinion on how the DB structure could be improved, I’d love to hear it. Also, any comments on the article are welcome!

Catch you in the next part!