Web Development · Advanced level

Advanced level · Module 1: Databases for Web Applications · Lesson 1 of 4

Modelling Data for the Web

Design tables, keys and relationships for a web application's data.

In this lesson you will

  • Identify entities and relationships from requirements
  • Design tables with primary keys, foreign keys and constraints

Key concepts

entityone-to-many and many-to-manyprimary and foreign keysconstraints

The lesson

Start from the requirements: a booking platform has customers, services, staff and bookings. Each entity becomes a table; each record has a primary key.

Relationships become foreign keys: a booking references a customer and a service (one-to-many). A many-to-many relationship, such as staff offering many services, needs a joining table.

Let the database protect the data: NOT NULL for required fields, UNIQUE for emails, CHECK for valid values and foreign keys to prevent bookings for services that do not exist.

Step by step

  1. List the booking platform's entities.
  2. Draw the relationships.
  3. Write CREATE TABLE statements with keys and constraints.
  4. Insert sample data and test the constraints.

Practice activity

Design and document the schema for the booking platform.

Expected result: A schema diagram and SQL script where constraints reject invalid sample data.

Check your understanding

  1. How is a many-to-many relationship represented?

    Show answer

    With a joining table holding foreign keys to both tables.

  2. Why add constraints in the database?

    Show answer

    They protect data integrity even if application code has bugs.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.