CRUD matrix and the value add

For a quick definition of a CRUD matrix visit Wikipedia. So how does a matrix help in process design? It helps from the very beginning. Starting with swimlanes, PPT, or Visio process designs, it can help you define your DAL. One thing I found handy when doing the matrix and process design is to have Excel open. This will allow you to put down some generic stored procedure names for DAL definition.

As a quick side bar, why stored procedures? It really helps with security. It's harder to crack a DB when using stored procedures than a select *. Plus it helps with data integrity. You have a defined data set going in or out. We create a procedures based on the CRUD. One for each letter.

Step 1 for us, create your header in Excel with 6 columns: 1. Table Name, 2. Drop Table, 3. Create, 4. Read, 5. Update, 6. Delete. In the first column, if you already have a DB and tables created, list out all the tables here. If you have not created a DB yet, this is perfect. Start with what you think you will need, for example, Project table, user table, customer table, project invoice table, user department table, etc....I think you get it from here.

Next, draw out some swimlanes, my suggestion is 3 lanes, Process, DB, and UI (Visio works perfect for this, but PPT works good too). Let me just note, this isn't perfect swimlane theory here, this is just a brief look at a way. In the top lane, map each process step. We are not putting a lot of detail. This is 10 mile up view. Step one may be Create document (manual task), step two is publish document to SharePoint (automated task), step 3 is write document data to DB (automated task), and so on.

So we'd create a second swimlane now called DB. In the DB lane we'll draw a line down from step 3, for create document in DB. This will required a Create Doc stored procedure. So now we have a procedure creating something...but where. So we flip back over to a spreadsheet and the list of tables. In which table or tables does all the data go? For the sake of this post, we'll say this is project data.

Step 1, create a procedures box in the spreadsheet and lets name this procedure, SP1 - create project doc. Now over in a matrix, we'll write SP1 in the Create column on the project table row. Depending on what is all contained in the project doc creation, you may also be writing to a secondary project doc table, a project customer table, and maybe even a project user table. All depends on how you have your table structure setup or this is a good what to start that design.

In the third lane, we use this for HTML page creation. As we go through, we may be able to create and reuse some UI pages. This just helps us document where all the interactions appear.

Basically you will continue to follow along on the Matrix spreadsheet and swimlane mapping until you have completed all your processes. It's a huge effort, but it will save time later in the project and it helps you know what to build for the DAL and UI. I will draw up some imagery and post it to a separate Web site and back link soon.