A CRUD Matrix


Background to a CRUD Matrix

CRUD stands for :-
Create, Read, Update and Delete.

A CRUD Matrix is very useful for identifying the
Data Items which are involved in the Data Access for each Application.
In the early stages it is not necessary to produce a complete CRUD Matrix, and
this example is included to provide guidance and act as a Template for the future.

SQL consists of only 4 statements, sometimes referred to as CRUD :-

Create - INSERT - to store new data

Read - SELECT - to retrieve data

Update - UPDATE - to change or modify data.

Delete - DELETE - delete or remove data

A CRUD matrix is a table showing the Functions in an application containing SQL statement affecting parts of a database.

The CRUD Matrix is an excellent technique to identify the Tables in a Database which are used in any User interaction with a Web Site.
CRUD means ‘Create, Read, Update or Delete’, and the CRUD Matrix identifies the Tables involved in any CRUD operation.
It is very valuable to combine a CRUD Matrix with the analysis of possible User Scenarios for the Web Site.
The analysis helps to identify any Tables which are not used, and any Tables which are used heavily, and may therefore be a performance bottleneck.
The application of this technique to a Portal Web Site is shown below.
It identifies the different kinds of Users who can access the Web Site.
For each kind of User, the User Scenario is defined as a series of User Actions, and the corresponding Tables are identified. This Table shows the User Scenarios for a Casual User.

Here is a simple CRUD Matrix for a CRM Data Architecture :-

Contact History

Maps

Points Of Interest

Preferences

Plan Route

Read & Create

Read

Read

Request Hotel

Read

Read

Visit Web Site

Read & Update

Read & Update

Here is a typical CRUD Matrix for a Business Intellignece Data Architecture :-

Nr.	USER ACTION	Create	Read	Update	Delete
1	Enter the Web Site	---	---	---	---
2	Complete User Survey	UserSurvey	SurveyMaster	---	---
3	Search the Site	---	Site Directory	---	---
4	Go Shopping	Orders, ProductOrders, ProductOrderOptions	Products, ProductOptions, Merchants	ID Table	---
5	Post to the Message Board	Comm_messages	Comm_messages	ID Table	---
6	Check the News Items	---	News	---	---
7	Leave the Web Site	---	---	---	---

	Contact History	Maps	Points Of Interest	Preferences
Plan Route	Read & Create	Read	Read
Request Hotel		Read	Read
Visit Web Site			Read & Update	Read & Update