Basic operations
Summarising what you already know, there are four basic operations that can be performed on databases. They can be remembered using the acronym CRUD which stands for Create, Read, Update and Delete. In SQL these operations correspond to the three type of DML command along with the SELECT statement. They are so fundamental that they are also built into other standard technologies such as the http protocol which defines the operations shown in the table below.
| Operation | SQL | HTTP |
|---|---|---|
| Create | INSERT | POST |
| Read | SELECT | GET |
| Update | UPDATE | PUT |
| Delete | DELETE | DELETE |
At the very least, an appilcation must support the CRUD operations. For example, the HR schema contains seven tables:
- COUNTRIES
- DEPARTMENTS
- EMPLOYEES
- JOBS
- JOB_HISTORY
- LOCATIONS
- REGIONS
A very simple interface would provide a way to view each table independently and would allow the user to change the data by inserting, updating and deleting records as required. Many application frameworks now provide this functionality by default and this is essentially what is provided by SQL Developer; however, a basic CRUD interface is very limited. For example, some operations should be performed automatically by the application rather than by the user. Consider the records in the job_history table: a new record should be automatically inserted when an employee record is updated with a new job title. Such additional functionality typically needs to be built into the application, although simple operations can be provided by the database y using triggers.
A further issue is that a CRUD interface does not present the data in a particularly useful form. Consider the tables departments and employees: the data is clearly related, and in certain circumstances it would be useful to present the list of employees alongside the details of the department they work for. This would simply be a view based on the one-to-many relationship between departments and employees. To accommodate this natural relationship, a commonly used design pattern is the master-detail page which presents one record from the parent table along with the related records from the child table. If the user navigates to the next row in the parent table, the detail records from the child table will automatically synchronise to show the related records:
The master-detail pattern is one of several recommended by Oracle for database-driven applications. The link above will take you to a brief description.
Alta UI Patterns Introduction