SQL Standards

New in ACS4

• Joins against the acs_objects table should called first in all queries (e.g., when creating views) because Oracle will drive the queries by the rightmost table if it cannot find better information from the cost-based optimizer.

ACS General Standards

• The data model is commented at the top with the current maintainer/author's name, email address, and a brief statement of purpose.

• Comments are present and accurate for any part of the data model that is less than self-explanatory.

• The data model is in 3rd Normal Form, i.e. "Every nonkey column provides a fact about the key, the whole key, and nothing but the key." If there are deviations, i.e. parts of the data model are de-normalized, there should be clear comments and justifications for breaking normal form.

• Column names are sensible and conform to ACS standards. For pre-ACS 4 work, tables that will likely be audited should all have the auditing standard columns (creation_date, creation_user, modified_ip_address, etc.)

• Data model constraints are named according to aD standards, and reflect the purpose towards which they are implemented.

• The _map suffix is used for many-to-many mapping tables.

• varchar primary keys are avoided wherever possible.

• There is a script available to drop the data model cleanly.

  Optional

• An Entity-Relationship diagram is available to supplement the written data model.

• Appropriate indices have been defined - ensure that potential performance bottlenecks are at least recognized if not addressed.

SQL File Conventions

1. Put all SQL files in /packages/<package-key>/sql/

2. Create one (and only one) -create.sql file, called <package-key>-create.sql file in that directory. The APM will automatically try to load any -create.sql file, so only include one.

3. Have this file source all other .sql files in the /sql directory by including @@ filename in the .sql file. @@ means load this file in the directory that the current file is located. So, if you want to load a file called demo.sql, just do:

         @@ demo.sql
         

Data Modeling

Be certain that there are no circular dependencies in your .sql files. One way to reduce these is to separate package declarations from package bodies and make sure that the package declarations get loaded first. A nice convention is to put all packages in -packages.sql, so that a programmer can easily scan a single file to check the PL/SQL API.

• If you need to store the names of database tables as column values (e.g., in the on_which_table column of the general_permissions table), normalize them into upper case (following the convention established in the Oracle data dictionary).

SQL

select * from... is almost always trouble - the only place it might be useful is for views, and seldom even then. Specify column names explicitly.

Formatting and style must be consistent.