Databases Reference
In-Depth Information
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 deine a short three- or four-letter alias.
• Foreign key column names are constructed as follows:
1.
The alias of the join table name is postfixed with
id
.
2.
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. Deine all the
id
columns as the
RAW(16)
columns and use
sys_guid()
as a default value for the
id
column. For example:
CREATE TABLE t
( id RAW(16) DEFAULT sys_guid() PRIMARY KEY
, column_a VARCHAR2(10)
, column_b VARCHAR2(10)
…
)
/
Creating the database objects
The irst 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.
