Where Merise comes from
Merise is a French method for analysing and designing information systems, developed in the late 1970s at the request of France’s Ministry of Industry. It is still taught in French technical and university courses because it addresses a lasting question: what data are we dealing with, and how should it be organised?
For data modelling, Merise progresses through levels. The conceptual model describes the domain. The logical model derives tables from it. The physical model adds types for a specific database system. Each level follows from the previous one through transformation rules, helping you spot design errors before writing SQL.
The data dictionary
Start by listing the information to manage in a data dictionary. Define each property once, with a name and type. A data item belongs in one place in the model; duplicating it creates the risk of conflicting values.
Consider a library. Useful data includes a member number, member name, book title, ISBN and borrowing date.
Entities
An entity is an object in the domain whose information you want to store, such as a member, book or room. It has attributesfrom the dictionary, including an identifier that distinguishes each instance from all others.
Two entities are enough to start our library example.
An identifier can contain several attributes when one is not enough. For example, a cinema screening can be identified by its room and start time.
Relationships
A relationship connects entities. It is named with a verb describing what happens between them: a member borrows a book. Relationships can also have attributes when information belongs to neither connected entity on its own.
The borrowing date is a good example: it is not a property of the member or the book alone. It belongs to the event that connects them.
Cardinalities, at the heart of the model
Each connection between an entity and a relationship carries a pair of numbers: its cardinality. Read it from the entityto answer a precise question:
What is the minimum and maximum number of times one instance of this entity can participate in this relationship?
In our example, a member may never have borrowed a book, or may have borrowed many, giving 0,n. A book copy is borrowed by exactly one member at a time, giving 1,1.
These four pairs cover most everyday cases.
| Pair | Meaning |
|---|---|
| 0,1 | may participate zero or one time |
| 1,1 | participates exactly once |
| 0,n | may participate zero or many times |
| 1,n | participates one or many times |
From MCD to relational model
Transformation into the logical model follows defined rules. When the conceptual model is correct, these rules determine the tables.
Each entity becomes a table
Its attributes become columns, and its identifier becomes the primary key.
A relationship with a 1,1 or 0,1 side produces a foreign key
Place the foreign key on the side whose maximum is 1. In our example, a book participates once, so the Book table receives the member number. The relationship’s attributes follow it: the borrowing date is stored in Book.
A relationship with n on both sides becomes a table
When both connections have a maximum of n, neither entity table can hold all the relationship’s values. The relationship becomes a separate table with a primary key combining the connected entities’ identifiers. Its own attributes become columns.
Member ──(0,n)── Borrow ──(1,1)── Book
Member (memberNumber, memberName)
Book (isbn, title, #memberNumber, borrowedOn)
If the library kept a borrowing history, each book could have been borrowed many times. Both connections would have a maximum of n, and Borrow would become a table:
Member (memberNumber, memberName)
Book (isbn, title)
Borrow (#memberNumber, #isbn, borrowedOn)
From relational model to SQL
Now choose a database system. Concrete types appear at this stage: text becomes VARCHAR(255) or TEXT, an integer becomes INT, and a date becomes DATE. The conceptual model remains independent of these storage types.
CREATE TABLE Member ( memberNumber INT NOT NULL AUTO_INCREMENT, memberName VARCHAR(255) NOT NULL, PRIMARY KEY (memberNumber) ); CREATE TABLE Book ( isbn VARCHAR(255) NOT NULL, title VARCHAR(255) NOT NULL, memberNumber INT NOT NULL, borrowedOn DATE NOT NULL, PRIMARY KEY (isbn) ); ALTER TABLE Book ADD CONSTRAINT fk_livre_adherent FOREIGN KEY (memberNumber) REFERENCES Member (memberNumber);
Practise the method
You learn Merise by modelling. In Meriz, you draw the MCD, validate its rules and see the generated logical model, physical model and SQL. Changing a cardinality immediately shows its effect on the final database, helping you understand why it matters.