Last updated on April 13, 2022
What is CRUD in Web Application?
CRUD is an acronym for Create, Read, Update, and Delete data, which are the four most commonly used operations for interacting with the database of a web application. One of the pivotal purposes for one to interact with a web application is to exchange and retrieve information, and CRUD plays an important role in serving that particular purpose. To implement a fully-functional application, one must guarantee CRUD. Users should be able to
- create and successfully save data
- retrieve, read, and possibly search for the corresponding data
- make changes and edits to the data
- eliminate and remove certain data.
Another technical concept that is usually implied when developers talk about CRUD is persistent storage. Contrary to data being temporarily stored on the front end (browser, internal caching), persistent storage provides access to backend data that remains stable and persistent even after the storage device is powered off. This persistent storage guarantees that whenever users perform CRUD on data, the changes are reflected in the backend database.
The Importance of CRUD in Web Applications
On the Front End
CRUD is an idea that is important for both front-end interactions as well as backend database/database design. On the front-end user interface, CRUD enables many of your interactions with the internet, things such as searching on Google, registering a user account on an e-commerce website, subscribing to an email list, editing a Wikipedia search, deleting a message, bookmarking a browser tab, etc. To explore the usage of CRUD in the front end, let’s examine the application of CRUD in one of the most trending social media platforms, LinkedIn.
Using Netflix CEO Reed Hastings’ LinkedIn profile page as an example,
Creation: Reed Hastings can upload his profile picture, input his location and title; while other end-users can follow or connect with Reed Hastings, share his profile in a message or save his profile to a PDF.
Read: All end-users, including Reed Hastings, can view Hasting’s profile picture, location, work title, and recent activities. Reed Hastings himself may have the additional access to data on users who have landed on his page and viewed his profile.
Update: Reed Hastings himself, can choose to update his profile picture, change his location and title; while other end-users can make decisions to unfollow or remove the connection with Reed Hastings.
Delete: The distinction between Reed Hastings and other end-users might be the greatest on the delete action. While Reed Hastings can delete all the information on his profile page, other end-users have no access to his profile. Here we can see a user access hierarchy on the delete action.
On the Back End
In addition to user actions on the front-end interface, CRUD employs persistent storage and backend database design on the backend. Different organizations need persistent storage to store different data on web applications; an e-commerce company may want to save user email addresses and past purchase history for marketing purposes. PayPal saves users’ card information to provide convenient online payment service, Gmail and Outlook save users’ email correspondence and frequent contacts, etc. This data is created by users on the interfaces and then permanently stored in the backend database. There are different ways to organize data in the backend database, such as relational databases, document-oriented databases, graphical databases, hierarchical databases. In this post, we focus on the most commonly used relational database. Data in relational databases are organized in tables of columns and rows, and data are related and constrained by pre-defined relationships using primary and foreign keys. It is similar to Excel sheet tables that link to each other using one or more common columns, which are called foreign keys.
Using LinkedIn as an example again, there are three relational tables in this database to keep track of LinkedIn users’ profiles, their schools, and companies. The Profile table contains User_ID, User_Name, School_ID, Company_ID, Location, Description, and Connection. The User_ID is created to ensure there are no duplicate profiles because there can be users with identical names. Therefore we call the User_ID primary key, which is the column that guarantees unique entries in a table. The School table contains School_ID andSchool_Name, where School_ID is the primary key for the School table. The Company table contains Company_ID and Company_Name, where Company_ID is the primary key for the Company table.
We use foreign keys to connect the three tables, which are the common primary keys: school_id and company_id are used as foreign keys to connect the Profile table with the School and Company tables. For Reed Hastings’ profile, the User_Name “Reed Hastings” corresponds to School_ID 1 and Company_ID 1, which are linked to Stanford University and Netflix in the School and Company tables. The User_Name “Susan Wojcicki” corresponds to School_ID 3 and Company_ID 2, which are linked to the University of California, Los Angeles, and Youtube in the School and Company tables. To achieve CRUD in the relational database using the SQL language, we can do the following queries.
To create data, we can use the insert query. INSERT INTO table_name (column1, column2…) VALUES (value1, value2 …);
For example, we can insert a new company by running the query: INSERT INTO Company(Company_ID, Company_Name) VALUES (7, “Meta”);
To read data, we can use the select query. SELECT column1, column2, …
FROM table_name.
For example, we can view every company on LinkedIn by running the query: SELECT Company_Name FROM Company;
In most cases, instead of selecting all data in a table, we want to select those that satisfy certain conditions. By using the WHERE keyword, we can add conditions to the select query. SELECT column1, column2, …
FROM table_name WHERE conditions;
For example, we can select the person’s name and location whose description is “CEO at Uber ” by running the query SELECT User_Name, Location FROM Profile WHERE Description = ‘CEO at Uber’;
To update data, we can use the update query. UPDATE table_name
SET column1 = value1, column2 = value2, … WHERE condition;
For example, we can update Reed Hastings’ location to Mountain View by running the query: UPDATE Profile SET Location = ‘Mountain View, California’ WHERE User_Name = ‘Reed Hastings’;
To delete data, we can use the delete query DELETE FROM table_name WHERE condition;
For example, if Susan Wojcicki decided to delete her LinkedIn profile, we can delete her profile by running the query DELETE FROM Profile WHERE User_ID = 2
UI Conventions that Facilitates CRUD
There are many user interface design conventions that facilitate CRUD on the front end. These user interface design practices can further maximize the usability of the web application by enabling smooth and intuitive data processing.
Create:
- The entry point to creating new data is important. If your web application relies on new user content, such as messaging platform, social media, email system, blog, restaurant reviews, the entry point needs to be in an obvious and intuitive position. It could be a “+” sign on the bottom of the screen center, a “Create New” button on the upper left corner or a long text input box.
- Autocompletion/pre-filled fields can help keep users engaged.
- Be clear and transparent about whether certain inputs can be edited later. If certain information could not be altered later on, such as username on social media or email address, inform the users right away.
- Only ask the users for necessary information. The rest can be modified later on.
Read
- Some elements to consider for viewing are grid, horizontal slidable windows, vertical scrollable windows, lists, and cards. Depending on what web application you are designing, you should choose the most appropriate viewing element. For example, if you are designing an ebook mobile application, a horizontal scrollable window might be the best choice for your mobile user because it is natural for users to slide their fingers horizontally in the range of the width of their phones. On the other hand, if your web application intends to display a lot of numerical data, a grid-like view might be most straightforward for the users.
- Conventions that facilitate search function:
- Search by keywords; highlight the search text in the returned search research
- Autosuggestion
- Utilize tags or filters
- Conventions that facilitate information organization:
- Enable the users to sort the information by date, price, color, location, or any other relevant information [3]
- Enable the users to mark useful information and save it for future use
Update
- Similar to data creation, the entry point is also important for updating data. Whenever users encounter editable data, the entry point should be intuitive and obvious.
- Inform users of the repercussions of the updates
- Leave an option for the users to discard the change and revert back to the original versions
- Generate automatic updates to mark something as read/visited/previously viewed by the users
Delete
- The delete action can take many forms. Depending on what web application you are designing, you should choose the most proper deletion. You can have a delete button when there are adequate white spaces, or you can have users select multiple items and then perform the deletion, just like in iPhone photo albums. Moreover, you can design deletion such that users would first swipe left then tap on a delete button. Or you can utilize drag and drop functionality to have users move certain items into a trash bin.
- Return a response that informs the users that the deletion was successful
- Warn users of any potential repercussions after deletion
- Inform users of mechanisms to revert the deletion [4]
Above are the conventions and tips on creating a CRUD web application! In this blog post, we explained the definition of CRUD and explored its importance in web applications. By looking at Reed Hastings’ LinkedIn page as an example, we realized the pivotal role CRUD played in the world of internet — to make the process of information exchange and storage more efficient and convenient. We also learned about how to take care of data in the backend using the relational database; primary key and foreign key connect information schema and create complex relationships.
