Codeskill

Learn to code, step by step

Modelling real domains – entities and relationships on paper first

Modelling real domains before you write SQL. The goal is a clear picture of entities, relationships, and rules – on paper or a whiteboard – so your tables make sense six months later.

Start with the problem, not the tables

When you learned MySQL basics, you probably created tables as you went: a users table here, an orders table when you remembered. That works for exercises. On a real project – a shop, a booking system, a blog with comments – you need to know what the system tracks before you pick column names.

Read the brief. List the nouns: customer, product, order, appointment, post, comment. Those are candidate entities. List the verbs: places, contains, belongs to, cancels. Those hint at relationships. Only then think about tables and keys.

Entities and attributes

An entity is something you store data about. Each entity gets a table (usually). Attributes are the facts you need to record: a customer has a name and email; a product has a title and price.

Sketch entities as boxes with attributes listed inside. Do not worry about SQL types yet – focus on meaning. Ask: is this attribute always one value per entity, or can it repeat? If it repeats, it probably belongs in another table (we cover that properly in the normalisation tutorial).

Relationships in plain language

Relationships describe how entities connect:

  • One-to-many – one customer places many orders
  • Many-to-many – students enrol in many courses; courses have many students
  • One-to-one – one user has one profile (less common, often merged or split for a reason)

One-to-many is the everyday case. You put the foreign key on the “many” side: orders.customer_id points to customers.id.

Many-to-many needs a junction table: enrolments with student_id and course_id, each referencing the parent tables.

Example: a simple shop

Entities:

  • customers – id, name, email, created_at
  • products – id, name, price, stock
  • orders – id, customer_id, placed_at, status
  • order_items – id, order_id, product_id, quantity, unit_price

Notice order_items captures the many-to-many between orders and products, with extra facts (quantity, price at time of sale) on the junction. Storing unit_price on the line item preserves history even if the product price changes later.

CREATE TABLE customers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  stock INT UNSIGNED NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  placed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status ENUM('pending', 'paid', 'shipped', 'cancelled') NOT NULL DEFAULT 'pending',
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Cardinality and optional links

Not every relationship is required. An order must have a customer; a product might have an optional category. Mark optional foreign keys as nullable: category_id INT UNSIGNED NULL.

Be explicit about delete behaviour later (cascade, restrict, set null) – we cover constraints in a dedicated tutorial. At modelling stage, just note what should happen when a parent row disappears.

Naming and consistency

Pick a convention and stick to it:

  • Table names plural (orders) or singular (order) – either is fine; mixing is not
  • Primary keys named id or order_id on the parent – pick one pattern
  • Foreign keys named customer_id, not cust or cid, unless your whole team agrees otherwise
  • Timestamps: created_at, updated_at are widely understood

Checklist before CREATE TABLE

  • Can you list every entity and why it exists?
  • Does each attribute belong to exactly one entity?
  • Are many-to-many links resolved with a junction table?
  • Have you noted business rules (unique email, order must have at least one line item)?
  • Would another developer understand the diagram without you in the room?

Good modelling makes normalisation, indexes, and queries easier. The next tutorial walks through normalisation in practice – and when a little denormalisation is acceptable.

PreviousGoing further with MySQL