Dátový modelThe data model

Today you design the data model of your application: the list of tables and relationships from which the code will grow in the following weeks. It is the first assignment (cv01). The data model terms are in the table of terms in week 1.

What a data model is

A data model is the list of database tables and the relationships between them. It is designed before the first line of code is written, because the code follows the tables: every table gets its own class (a model), every relationship its own methods. A badly designed table is hard to fix in week eight; today it is one sentence in the README.

In this course we work with three kinds of tables:

The main entity is the table the application revolves around. It has many columns; users add, edit and delete its records. In the sample it is recipes. In a tool rental it would be loans, in a library books, in a match record matches.

A lookup table is a small table with a fixed list of values the user chooses from: categories, units, difficulties, types. It usually has only id and name. Lookup tables are managed by an administrator; a regular user only picks from them in a drop-down list.

A pivot table (junction table) connects two tables when one record can belong to several records of the other table and vice versa. A recipe has several ingredients; an ingredient is in several recipes. The pivot table holds pairs of ids and possibly its own data about the pair (amount, date, note).

Keys and relationships

Every table has a primary key, the column id, which identifies a record uniquely. The database assigns it itself (AUTO_INCREMENT).

A foreign key is a column containing the id of a record from another table. The column difficulty_id in the table recipes contains an id from the table difficulties. The database makes sure it can never hold a number that does not exist in the other table.

Depending on how tables are connected, we distinguish two kinds of relationships:

One-to-many (1:N): one record of the first table belongs to many records of the second. One difficulty has many recipes; each recipe has exactly one difficulty. It is solved by a foreign key directly in the table on the N side: recipes.difficulty_id.

Many-to-many (M:N): many to many on both sides. A recipe has several categories; a category has several recipes. This does not fit into either of the two tables, so a pivot table recipe_categories with two foreign keys is created.

What happens on delete

For every foreign key the database needs to know what to do when the record it points to is deleted. The ON DELETE rule decides:

• RESTRICT: the delete is refused. A difficulty cannot be deleted while any recipe uses it. This is the default behaviour and it is right for lookup tables.
• CASCADE: dependent records are deleted together with it. When a recipe is deleted, its rows in the pivot tables go too, because a link without a recipe makes no sense.
• SET NULL: the foreign key is set to NULL. The record stays, it only loses the link. Used for optional links.

When designing the model, write for every foreign key which rule applies and why. We will ask about it at the defence.

The shape of your project

Your data model must have the same shape as the sample, so that every week you can transfer the procedure from the sample to your project:

• one main entity,
• three to four lookup tables, at least one of them connected to the main entity directly (a 1:N relationship through a foreign key) and at least one through a pivot table (an M:N relationship),
• two pivot tables, at least one of them with its own attribute (amount, date, count, note).

The users table is added in week 7; do not design it now. The topic must not be recipes or food. Examples of topics that fit the shape: a tool rental (loan × tool with a return date), a match record (match × player with goals scored), a library (loan × book with a due date), a training plan (workout × exercise with repetitions), a car service (job × operation with a price), a game collection (game × genre, game × platform with a release year).

Example 1: the data model of the sample project

The sample project is recipes. This is its data model, in exactly the shape your project should have. A 1:N relationship once, an M:N relationship without attributes once, an M:N relationship with attributes once.

The same model written as text:

recipes                      main entity
  id, title, servings, prep_time, instructions, created_at
  difficulty_id  → difficulties.id     1:N, ON DELETE RESTRICT

difficulties                 lookup table, connected directly (1:N)
  id, name

categories                   lookup table, connected through a pivot (M:N)
  id, name

ingredients                  lookup table, connected through a pivot with attributes
  id, name

units                        lookup table, used as a pivot attribute
  id, name, abbreviation

recipe_categories            pivot table without attributes
  recipe_id    → recipes.id           ON DELETE CASCADE
  category_id  → categories.id        ON DELETE RESTRICT

recipe_ingredients           pivot table with attributes
  recipe_id      → recipes.id         ON DELETE CASCADE
  ingredient_id  → ingredients.id     ON DELETE RESTRICT
  amount, note
  unit_id        → units.id           ON DELETE RESTRICT

Why these rules: a recipe may be deleted at any time and its rows in the pivot tables go with it (CASCADE). A category, an ingredient or a unit cannot be deleted while any recipe uses it (RESTRICT); otherwise recipes would keep references to values that no longer exist.

In README.md on GitHub the diagram is drawn with the Mermaid notation (erDiagram). GitHub renders it as a picture; in the editor it is plain text. This is the recipes model in Mermaid; your README will have exactly such a block for your topic:

erDiagram
    recipes ||--o{ recipe_categories : ""
    categories ||--o{ recipe_categories : ""
    recipes ||--o{ recipe_ingredients : ""
    ingredients ||--o{ recipe_ingredients : ""
    units ||--o{ recipe_ingredients : ""
    difficulties ||--o{ recipes : ""
    recipes {
        int id PK
        string title
        int servings
        int prep_time
        text instructions
        datetime created_at
        int difficulty_id FK
    }
    difficulties { int id PK  string name }
    categories { int id PK  string name }
    ingredients { int id PK  string name }
    units { int id PK  string name  string abbreviation }
    recipe_categories { int recipe_id PK,FK  int category_id PK,FK }
    recipe_ingredients { int recipe_id PK,FK  int ingredient_id PK,FK  decimal amount  string note  int unit_id FK }

Example 2: an AI prompt for designing the data model

You let the AI design the model, but you must write the request so that the result has the required shape. The prompt therefore lists exactly what the model must contain and in what form the answer should be. We do not ask for SQL, that is next week’s topic; today we want a list of tables with columns and an explanation of relationships that you understand and can defend.

Design a relational database data model (MariaDB) for a web application on the topic: [FILL IN THE TOPIC].

The model must have exactly this shape:
- one main entity into which the user adds, edits and deletes records,
- three to four lookup tables (small tables with a fixed list of values, columns id and name),
  at least one of them connected to the main entity directly by a foreign key (1:N relationship)
  and at least one connected through a pivot table (M:N relationship),
- two pivot tables, at least one of them with its own attribute
  (for example amount, date, count or note).
Do not design a users table yet.

For every table write: the name in English in plural (e.g. tools, loans),
the list of columns with data type and a short description,
the primary key and the foreign keys.
For every foreign key write the ON DELETE rule (RESTRICT, CASCADE or SET NULL)
and justify in one sentence why this one.

Write the answer as Markdown: a heading and a column table for each table,
then a list of relationships (which table, with which, what type)
and at the end an ER diagram in Mermaid notation (a ```mermaid block with erDiagram)
containing all tables with their columns and all relationships.
Do not write SQL or PHP, only the design.

Why the prompt is written this way: the shape of the model is listed point by point so that the AI cannot leave out the pivot table with an attribute or connect all lookup tables through foreign keys. We ask for table names in English in plural because that is what the classes in the code will be called. We ask for the ON DELETE justification because you will explain it at the defence. The output is Markdown so that it can be pasted directly into README.md, and we ask for the Mermaid diagram at the end because GitHub renders it in the README as a picture – you will see your model drawn without any extra tool.

You also get the tasks as the assignment cv01 in Classroom 50; they are in the file README.md in your repository.

Link to the assignment cv01: accept the assignment (applies to B. Stehlíková’s groups; other groups get the link from their own lab teacher)

code-server --install-extension Natizyskunk.sftp --install-extension yandeu.five-server --install-extension bmewburn.vscode-intelephense-client --install-extension mblode.twig-language-2