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
- List the booking platform's entities.
- Draw the relationships.
- Write CREATE TABLE statements with keys and constraints.
- 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
-
How is a many-to-many relationship represented?
Show answer
With a joining table holding foreign keys to both tables.
-
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.