From a business question to a relational model

Start with facts and relationships

A database stores structured information so an application can retrieve and change it consistently. A spreadsheet is useful for many tasks, but repeating a client's name and email in every project row makes updates difficult. If the email changes in one row and not another, the system no longer has one dependable fact. Relational modelling separates entities and connects them through identifiers.

Use a fictional workshop throughout this course. It has clients, projects, quoted items and payments. A client can have many projects, and each project belongs to one client in the initial model. A project can contain many quoted items. These are business rules, not inevitable laws of software. If the real business later allows joint clients or multiple billing contacts, the model needs a deliberate extension.

An entity is a thing whose information and identity matter independently. An attribute describes it. A primary key uniquely identifies a row; it should remain stable even when a display name changes. A foreign key references a permitted parent record and helps prevent orphaned relationships. An identifier is not proof of authorisation: knowing project 42 exists does not mean a visitor may view it.

Worked example: separate clients from projects

Begin with these conceptual records. The examples are invented classroom data, not actual B-tech customers. Names and email addresses illustrate the model without introducing private personal information.

clients
id | display_name       | email
1  | Classroom Workshop | owner@example.test

projects
id | client_id | title                  | status
10 | 1         | Workshop service site  | Received
11 | 1         | Product label concept  | Quoted

The client details appear once. Two project rows point to client 1. A query can combine them when a screen needs the name beside a project. This does not mean every attribute must be separated into another table. Store facts according to meaningful dependencies. A project title belongs to the project; a client's current email normally belongs to the client. An invoice may deliberately snapshot billing details because historical documents must preserve what was issued.

Cardinality describes how many records can relate. One-to-many is common, but many-to-many relationships require an association table. For example, a project may involve several service categories and a service category may appear in many projects. A project_services table containing project_id and service_id expresses each relationship explicitly. A unique pair prevents duplicate associations when the business permits only one link per pair.

Practice: write a data dictionary

List the entities for a simple service request platform. For each attribute, record its meaning, whether it is required, an example value and any business rule. Decide whether a deadline can be absent and what absence means. Do not store an unknown date as an invented date merely to satisfy a required field. Distinguish a missing price from a genuine zero price.

Draw relationships using plain labels or a diagram tool. Explain what should happen when a client with existing projects is removed. Possible policies include rejecting deletion, archiving the client or carefully anonymising data under an approved retention process. Automatically deleting every related financial record is rarely a decision to make casually.

Review the model using concrete questions: Can one client have two projects? Can a project exist without a client? Can two people share an email under this business policy? The answers determine constraints. A schema should reflect an explicit rule rather than a developer's unexplained preference.

Self-check and expected outcome

Your model stores each current client email once and connects projects through stable identifiers. It explains one-to-many and many-to-many relationships, distinguishes unknown values from zero and documents deletion policy. The first deliverable is a clear model and data dictionary, not a large collection of tables whose purpose nobody can explain.