We have set up the APEX environment, now let's focus on the database. For means of consistency and maintainability, it's a good practice to use standards and guidelines. In this section, we will describe some standards and guidelines for data modeling, database objects, and PL/SQL usage.
When the requirements are clear, we can create a data model. A data model provides the structure, definition, and format of the data.
We will translate the requirements into tables, columns, and relations. It will be the single point of truth of our system. Because our whole system is built upon this model, it's very important to spend sufficient time on it. A data model is also a great means to communicate with your customers and among developers about the system and design of the database. When a database is well designed, it can be easily maintained for future development.There are a number of Computer Aided Software Engineering (CASE) tools that you can use to create a data model. Besides data modeling, some of these CASE tools can also be used to maintain all the PL/SQL packages, functions, procedures, and trigger code that we use in our applications. Oracle itself provides Oracle Designer and Data Modeler from SQL Developer. The following diagram shows Data Modeler. One of the advantages of using such a tool is the ability to generate and reverse engineer the database creation scripts in an intuitive graphical manner.
SQL Developer Data Modeler
Relations between the tables are always in a one-to-many relationship; for example, a user can perform one or more searches. We can use colors to differentiate between tables that have a lot of mutations and tables that don't have any. Those tables can be candidates for creating lists of values (discussed later in this chapter).
A great example of standards and guidelines is Oracle's well-documented CDM RuleFrame. Database modeling standards and guidelines can be as follows:
Table names are written in plural.
Check constraints will be used for short domains on columns. If they are long or not know yet, we use a lookup table.
The primary key is always named as
id. This is useful when we want to write reusable generic code.For each table we define a short three- or four-letter alias.
Foreign key column names are constructed as follows:
The alias of the join table name is postfixed with
id.For every foreign key we define an index.
We use database triggers to populate the primary keys and use one sequence that will be used to populate all the primary keys. For the triggers, a script such as the following can be used for all tables:
CREATE OR REPLACE TRIGGER doc_bir BEFORE INSERT ON documents FOR EACH ROW BEGIN :new_id := NVL(:new_id,all_seq.NEXTVAL); END; /
An alternative to triggers and sequence is the use of
sys_guid(). On the Internet, a lot of information about the pros and cons for both approaches is available. Define all theidcolumns as theRAW(16)columns and usesys_guid()as a default value for theidcolumn. For example:CREATE TABLE t ( id RAW(16) DEFAULT sys_guid() PRIMARY KEY , column_a VARCHAR2(10) , column_b VARCHAR2(10) … ) /
The first thing we have to do is create the database schema, which will hold the database objects. We can use the SQL Workshop of APEX for creating the database objects, but its use is very limited compared to the specialized CASE tools.
The following objects can be created in the application schema:
Beside the tools for creating a model, we need some tools during the further development process, tools for accessing the database easily, and tools for web development. Without going into detail, we will just name a few tools that you can use.
Examples of database tools are:
Toad
SQL Developer
PL/SQL Developer
Tools for web development, HTML, CSS, and JavaScript are as follows:
Aptana
Firebug
Web Developer
Internet Explorer Developer Tools
Built-in tools in the browser
Versioning tools
Performance measurement tools
GUI design tools
Refer to Chapter 5, Debugging and Troubleshooting for the details on other tools.