Database standards and style guide

Well, not a complete list, but I had to send out a "here is how I do it" email this morning to a contractor who is "helping me out" (aka, he's smarter then me and can work faster then me and I better watch my back because he might take my job).

Dear Guru,

Not that it would help you, but here's a list of some of the guidelines I try to follow when doing a physical db model. Also below you'll find a style guide I created many years ago for PL/SQL. Please note that these are not "the law", it's just an attempt to let you know how I've been doing things.

I hope this all helps...Rich

Table naming convension:
I try to name tables with logical names and try not to to abbreviate too much. I also try not to prefix table names with the domain (i.e. I won't name a Department table for an HR system HR_DEPARTMENT, I just call it DEPARTMENT if I can).

Surrogate keys: I usually name a surrogate key by appending "_SEQ" to the end of the table name. I then go ahead and create the database sequence with the exact same name as the field (i.e. I would use DEPARTMENT_SEQ for the DEPARTMENT table for both the surrogate key and for the Oracle Sequence name).

If the natural key of a table is more then two fields I will add a surrogate key field (_SEQ) as the Primary Key and add a unique constraint on the two fields that comprise the natural key.

One thing I really try to do is make sure I continue to call a field the same thing throughout the model. An example would be to avoid using the field ID for the Primary key (surrogate key) in one table and then call it something different in another table (i.e. I avoid calling the key ID in the DEPARTMENT table and then DEPARTMENT_ID in the EMPLOYEE table). I hope this makes sense.

I name dates with a suffix of _DT and make sure they have the DATE datatype (i.e. CREATE_DT is the date the record was created)
I name indicator fields (boolean) _IND and I force values of NULL or Y or N
i.e. a field to determine if a person is an active employee in an employee table would be named ACTIVE_IND and would have a value of Y or N (or null if unknown).

I name Primary Keys by appending a _PK to the table name
I name unique indexes by appending a _UIDX to the table name (or _UIDX## if there are more then one)
I name foreign keys by appending a _FK## to the table name (i.e. EMPLOYEE_FK01)

PL/SQL Style Guide:

Adopt a consistent, readable format that is easy to maintain
Capitalization & Text formatting
Reserved words should be in uppercase (BEGIN, DECLARE, ELSIF)
Built-in Functions are in uppercase (SUBSTR, COUNT, TO_CHAR)
Predefined types are in uppercase (NUMBER(7,2), BOOLEAN, DATE)
SQL keywords are in uppercase (SELECT, INTO, UPDATE, WHERE)
Database Objects are in lowercase (log_table, classes, students)
Variable names are in lowercase
Adopt logical, consistent naming conventions for modules and data structures
Variable & Parameter Declarations
Variable names should be prefixed with one of the following
"gv_" for variables defined in a package specification
"v_" for variables defined in a package body
"lv_" for variables defined in Stored Procedures or Functions
Variable names comprised of multiple words have an underscore between
each word (v_employee_id)
Parameters should begin with "p_"
Rather then hard coding the data type of a variable/parameter, you should
use the %TYPE operator when possible
PL/SQL object naming standards ( describes the basic functionality contained within the PL/SQL Object)
PL/SQL stored procedures should begin with "sp_" (i.e. "sp_")
PL/SQL functions should begin with "fx_" or "get_" (i.e. "fx_" or "get_")
PL/SQL packages should end with "_pkg" (i.e. "_pkg")