What is CRUD? It stands for ‘Create, Read, Update, Delete’, and these are common, standard actions that are performed on the data in a relational database. Here are some alternative acronyms, basically covering the same concepts. It’s always worth bearing these actions in mind when thinking about what the database needs to do in its day-to-day operations.

I’m going to use the example of an address book application to make things easier to discuss. My example involves building an address book to store all of my contact information, and I’m thinking about the database design.

Typically, CRUD actions are applied to a record. In my address book application, a record would relate to a single contact, say for Anne Smith. The record has several fields, e.g. for storing things like first name (Anne), last name (Smith), email address etc. These completed fields for Anne together comprise her record.

A series of records is stored in a database table. All the records in the table have the same ‘format’ – i.e the same group of fields.

Create / Delete

To start off, we can create a new record for Anne. We’ll probably have a web form for filling in the various fields, and when we’re ready, we’ll click ‘submit’. This will then add a new record, for Anne, into the database table.

If we no longer need Anne in our list of contacts, we can delete her record from the database. However, another technique which is often employed is to keep the record, but mark it in one of its fields as ‘deleted’ or ‘archived’. That shows that the record is no longer current, but the data remains in the database. To keep things simple in my design, I’ll actually delete the record when it’s no longer needed.

Both Create and Delete actions change the number of records in the table.

Read / Update

In contrast, Read and Update actions do not change the number of records in the database. Instead, each of these actions are executed on an existing record.

In our example application, we can Read back Anne’s record, for example so we can display it in a web page. After we’ve read the record, we can format it nicely into a web page for viewing.

If we find out that Anne has changed her mobile phone number, we can Update it in the database. In this case, we’re updating the mobile phone number field in Anne’s record.

Very often an update command is preceded by a read command – so we can see the record before updating it. I’m going to do that in my application. Firstly, I’ll read the record and display it in the web page. Then I’ll allow the user to modify any fields, and I’ll have an ‘update’ button that will put the changes back into the record in the database.

Summary

CRUD is an incredibly simple concept, but always useful to keep in mind for any database application.

 

Neil Tubman, Terzo Digital, October 2017