CRUD Matrix
A CRUD matrix is a table that crosses a system's processes with its data entities. The processes or use cases take the rows. The entities take the columns. Each cell records what the process does to the entity: create it (C), read it (R), update it (U) or delete it (D). The analyst then reads the columns. Data with no creator, data with no reader and data with two creators each signal a missing requirement or a data quality defect.
Goal
The CRUD matrix checks that a system's processes cover its data. The use case model describes what the actors do. The data model describes what the system retains. Each can look complete on its own. The grid sets the two models against each other cell by cell and shows the gaps before the team freezes the requirements: an entity that nobody creates, a use case that reads data nobody ever entered.
The deliverable combines the filled grid and the list of findings. Each finding starts from a gap or a duplicate in the grid. It becomes a question to the business, then an added use case, a clarified requirement or an entity removed from the model.
Across an application portfolio, the same grid groups processes and data into subsystems. In maintenance, an entity's column lists the processes that a change to that entity touches.
Usage
When to use it
- Use cases and data model written: check that they cover each other before the requirements are validated.
- Completeness review of a specification: find the entity with no creator and the forgotten process.
- Data shared between several applications: spot the duplicates that arise when two systems create the same entity.
- Splitting a system or a portfolio: group processes and data into subsystems.
- Changing an entity in maintenance: list the processes to analyse and retest.
- Release planning: deliver the process that creates a piece of data before the processes that read it.
- Personal data in the model: justify each item collected by at least one process that reads it.
When not to use it
- Neither a process list nor a data model: model the data and write the use cases first. Without them the grid has no axes.
- A question of authorisation: which role may act is a matter for the roles and permissions matrix.
Description
Rows, columns and letters
The rows are the system's business processes or use cases. A row names an actor's goal ("Register a dog").
The columns are the entities of the entity-relationship diagram or the classes of the class diagram. One column per business entity is enough. Technical tables (logs, lookup tables) stay out of the grid.
Each cell holds zero, one or several letters. C records that the process creates an instance of the entity. R records that it reads one, U that it updates one and D that it deletes one. The letters are the initials of Create, Read, Update and Delete. An empty cell says that the process does not touch the entity. The legend sets the local conventions, for instance whether a U implies the R that precedes it.
Where the grid comes from
IBM's Business Systems Planning guide (1978) sets data classes against the processes of the enterprise. It enters only two letters: C for the process that creates the data, U for the process that uses it. The guide uses this grid to show how the processes share the data. It then derives the information architecture from it.
Information Engineering is a data-centred method for designing information systems. It spread in the 1980s and the early 1990s. According to Politano, its strategic information systems plan delivers the CRUD matrix together with the data model and the functional decomposition.
The BABOK devotes no technique to the process × entity form. Its glossary defines the CRUD matrix by roles: which user roles may create, read, update or delete which entities. It then concedes in one sentence that the same grid can show processes in place of users.
Building the grid
- List the processes or use cases at goal level. IBM orders them along the life cycle of the resource they manage. The process that creates a piece of data then comes before the processes that read it.
- List the entities from the data model. The analyst records any entity added at this stage in the data model. Otherwise the two documents diverge.
- Fill in row by row. For each use case, reread the steps of the main scenario and of the alternative scenarios. Record each entity that the step creates, reads, updates or deletes. Fill a row in one go. An empty cell in a filled row then means "no operation".
- Read the grid column by column, then row by row. Each anomaly becomes a finding addressed to the business.
Next to each letter the analyst notes the number of the step that justifies it. A reviewer who disputes an R then traces it back to the scenario step.
Reading the matrix
The grid's value lies in its gaps. Politano gives the basic checks: an entity generally carries at least one C and several Rs. Several Cs on one entity signal a possible data quality problem.
- A column with no C. No process in scope creates the entity. Either a use case is missing or the data comes from another system. In the second case the interface analysis must name that system and the flow.
- A column with no R. The system captures data that nobody uses. Either a consuming process is missing or the data should be removed. For personal data the question is also a legal one: the FADP (Art. 6 para. 3) allows collection only for a specific purpose that the data subject can recognise. Cantonal laws set the same principle for a municipality.
- A column with no U. No process keeps the entity up to date. Either an update use case is missing (the dog keeper's change of address) or the data stays fixed once recorded.
- A column with several Cs. Two processes create the same entity under rules that often differ. The business designates the authoritative source. The other processes read or update.
- A column with no D. No process ends the life of the data. The business sets its retention period and names the process that applies it. For personal data the FADP (Art. 6 para. 4) requires its destruction or anonymisation as soon as it is no longer needed.
- An empty row. The process touches no data in the model. Either the data model omits an entity or the process lies outside the scope.
Grouping processes and data into subsystems
IBM's guide carries the reading through to the architecture. The analyst reorders the columns: the data that the first process creates moves to the left, then the data of the next process. The Cs then fall on a diagonal. The analyst draws a box around each group of processes and the data they create: each box marks out a subsystem. A U left outside its box marks a data flow between two subsystems. The guide draws this flow as an arrow. Politano calls this reordering an affinity clustering of rows and columns.
Using the grid downstream
Politano lists impact analysis and estimation among the uses of the grid without giving an estimation method. For impact, the column of the changed entity lists the processes to analyse and retest. For estimation, the analyst assigns a weight to each letter and adds up the weights of the row. The organisation calibrates these weights on its past projects. No standard sets them.
The grid also orders the releases. Unless data is migrated, a use case that reads a piece of data can be tested end to end only once the use case that creates it has been delivered.
The boundary with the roles and permissions matrix
The same four letters fill another grid: the roles and permissions matrix. That matrix crosses a solution's functions with the roles that use it. Its cell sets what a role is allowed to do. The CRUD matrix crosses processes with entities. Its cell records what a process does to a piece of data. The first produces access control requirements. The second produces completeness findings.
The BABOK uses one name for both. Its roles and permissions technique refers to a "CRUD matrix" for the roles of IT systems. The title of a grid therefore does not say which matrix it is. Its axes do.
| Matrix | Rows × columns | What a cell says | Question settled | Deliverable |
|---|---|---|---|---|
| CRUD | processes or use cases × data entities | the operation that the process performs | do the processes cover the data? | completeness findings |
| Roles and permissions | solution functions × roles | the right that the system grants the role | who may act in the system? | access control requirements |
In software design, CRUD also names the basic operations of a database or an API. The matrix borrows these letters to analyse requirements.
Pitfalls
CRUD use cases
Rows such as "Create a dog", "Update a dog" and "Delete a dog" give a grid in which each row carries a single letter in a single column. That grid confirms the breakdown and checks nothing. The analyst writes the use case at the level of the actor's goal, then builds the grid.
R everywhere
An analyst who records an R wherever a screen could display the data fills every column. The check for a column with no R no longer fires. An R goes in when a scenario step uses the data.
The grid filled from memory
Filled from the names of the use cases, the grid reflects the analyst's intuition. Filled from the scenario steps, it reveals what the texts omit.
The finding with no addressee
A column with no C that was spotted and never put to the business remains a defect in the model. Each finding carries an owner and a due date.
AI considerations
A language model fills a first grid in seconds from the use case texts and a list of entities. It fills the gaps with plausible letters. Yet the gaps are the result of the technique.
For each letter, the analyst requires the model to cite the scenario step that justifies it. The analyst rejects any letter without a cited step. The model also checks a grid made by hand: it lists the failing columns and drafts the question to put to the business for each one. A spreadsheet counts the letters. The model words the questions.
On an existing system, a language model reads the stored procedures and the data access code. It extracts the SQL operations per table and proposes a grid of the system as it stands. The analyst compares this grid with the grid of the requirements. The comparison shows what the code does without a requirement and what the requirements ask for without code. The analyst validates how the technical tables and abbreviated names map to the business entities.
The stored procedures, the use case texts and the data model leave the organisation when the analyst sends them to an external model. The real data that the texts quote leaves with them. The analyst uses an instance that the organisation has approved or first removes the real data from the texts.
Examples
A municipality specifies its dog tax module. The grid crosses its use cases with the entities of its data model. The Rate column carries only an R.
| Use case | Keeper | Dog | Rate | Invoice |
|---|---|---|---|---|
| Register a dog | C R | C | ||
| Report a change of keeper | C R | U | ||
| Report a dog's death or departure | R | U | ||
| Cancel an erroneous registration | D | |||
| Bill the annual tax | R | R | R | C |
| Record a payment | R U |
No use case creates or updates a rate. Yet billing reads an amount every year. The finding puts a question to the finance department: who enters the annual rate that the municipal council approves? The answer adds the use case "Set the annual rate" (C, U on Rate). The finance department is its actor.
The other columns trigger checks as well. The Keeper column carries two Cs and no U. The business designates the authoritative source for it and decides which use case records a change of address. Only the Dog column carries a D. The business sets the retention period for the keeper, the rate and the invoice. The analyst enters each of these findings in the list.
Visualisations
The deliverable remains a table that can be sorted and filtered. The processes follow the order of the resource's life cycle. The letters in a cell follow the order C, R, U, D. Conditional formatting flags the failing columns. The reviewer sees the findings without counting letters. The list of findings accompanies the grid and refers to each column by its name.
Cost
| Dimension | Level | Justification |
|---|---|---|
| Preparation | Medium | The use cases and the data model must exist at the same level of detail. Where one of them is missing, the analyst produces it first. |
| Execution | Low | Filling the grid follows the scenario steps. A grid of about twenty use cases and fifteen entities takes half a day. |
| Documentation | Medium | The grid follows every change to the two models. Kept by hand, it diverges with every change that nobody carries over. |
Tooling
A spreadsheet is enough for a grid of about thirty rows. One formula per column counts each letter. Conditional formatting colours the failing columns. The spreadsheet does not track the models: the analyst carries over each change to a use case or an entity by hand.
Modelling tools produce the grid as a view that follows the changes to the model. Sparx Enterprise Architect shows the links between use cases and classes in its relationship matrix. The analyst enters the letters there. Visual Paradigm generates a CRUD table from the modelled processes and entities. These views fit once the models live in the tool.
For an existing system, static code analysis tools and data catalogues extract the operations per table. They give the grid of the system as it runs.
Sources
- IBM, Business Systems Planning: Information Systems Planning Guide, GE20-0527-2, 2nd edition, 1978: in chapter 7 the grid of data classes against processes with the letters C and U; in chapter 11 the diagonal grouping that marks out the subsystems and the data flows between them.
- Politano, A. L., Salvaging Information Engineering Techniques in the Data Warehouse Environment, Informing Science 4(2), 2001, pp. 35-43: the CRUD matrix as a deliverable of the Information Engineering strategic plan, its checks (multiple creates, the ratio of creates to reads, clustering) and its uses in impact analysis and estimation.
- IIBA, A Guide to the Business Analysis Body of Knowledge (BABOK Guide) v3, Glossary, entry create, read, update, and delete matrix (CRUD matrix): the definition by user roles and the extension to processes.
- IIBA, A Guide to the Business Analysis Body of Knowledge (BABOK Guide) v3, §10.39 Roles and Permissions Matrix: the reference to a CRUD matrix for the roles and responsibilities of IT systems.
- Swiss Confederation, Federal Act on Data Protection (FADP), SR 235.1, Art. 6 para. 3 and 4: the collection of personal data limited to specific purposes that the data subject can recognise, then their destruction or anonymisation as soon as they are no longer needed.