You can now query tables, change them, and protect them with rules. Before the Kladde gets tables of its own, there is a decision that is hard to change later: Which tables are there, which columns do they have, and how are they connected? That is called data modeling.

You can tell a good model by the fact that questions are easy to answer and wrong data has a hard time getting in. You tell a bad one only later: a company moves and then appears in the database with two addresses, or a simple question needs a query half a page long. In this lesson you design the Kladde’s model for the whole of K2, write it down as a diagram, and have it reviewed. Nothing is programmed in the process; the tables come into being in the next lessons with EF Core.

Entities and relationships

An entity is a thing that you want to store something about and that you have to keep apart: a contact, a company, an appointment. Every entity becomes a table, every single thing a row. What you know about it are its attributes, the columns.

Between entities there are relationships. Three kinds occur:

  • 1:n (one to many). A company employs many contacts, a contact belongs to at most one company. In the table, the relationship sits on the side of the “many”: Contacts.CompanyId refers to Companies.Id. That is how it was in the practice database practice.

  • n:m (many to many). An appointment can have several contacts, say a meeting with two people from the same company, and a contact has many appointments. A column ContactId in the appointment is no longer enough for that, because it holds only one value. An n:m relationship gets a junction table of its own, whose rows each connect one pair:

    AppointmentId ContactId
    1 1
    1 2
    2 1

    Appointment 1 is with contacts 1 and 2, appointment 2 only with contact 1. The junction table’s primary key consists of both columns, so every pair can occur only once, and both columns are foreign keys.

  • 1:1 (one to one). Rare, and usually a sign that two tables are really one. It doesn’t occur in the Kladde.

The Kladde from K1 already knows the n:m relationship: since lesson 1.10.2, Appointment.ContactIds is a list of numbers. In the database, that becomes a junction table AppointmentContacts.