When designing a database it's a good idea to follow some sort of naming convention. This will involve a little planning in the early design stages but can save significant time when maintaining the finished system.
For a system that is built to be deployed from the command line, you may have a set of SQL scripts that build every table, view, index, constraint etc from scratch. When updating or refactoring the design it can be an enormous advantage to simply search and replace one text item for another. However for this to be at all possible the names have to be unique - this is not easy to get right.
An example, you have the tables
If you want to search/replace the name of T_Staff to T_HR, this will fail because it will also match T_Audit_staff
If you had chosen the name T_Aud_staff then this search/replace will work.
In reality few people will blindly run Search and Replace across a large code base, but having reasonably unique names for things does make it faster to find and update all the references to it. If you have a table called staff with a staff column and a staff index and staff foreign keys, it will be harder to maintain.
Whatever convention you decide to follow, keep it simple or no one will use it.
Table names are plural, field name is singular
Table names should not contain spaces, words should be split_up_with_underscores.
The table name is limited to 30 bytes which should equal a 30 character name (try a DESC ALL_TABLES and note the size of the Table_Name column)
If the table name contains serveral words, only the last one should be plural:
There are pros and cons to adding a prefix or suffix to identify tables-
Pros: If most access will be made via VIEWS then prefixing all the tables with T_ and all the views with V_ keeps things organised neatly, you will never accidentally query the wrong one.
Cons: Suppose, your naming convention is to have the '_TAB' suffix for all tables. According to that naming convention, the APPLICATIONS table would be called APPLICATIONS_TAB. If as time goes by, your application gets a second login, perhaps for auditing, or for security reasons. To avoid code changes, you will then have to create a View or Synonym that points at the original tables and is confusingly called APPLICATIONS_TAB.
Ideally each field name should be unique within the database schema. This makes it easy to search through a large set of code (or documentation) and find all occurences of the field name.
The convention is to prefix the fieldname with a 2 or 3 character contraction of the table name e.g.
PATIENT_OPTIONS would have a field called po_patient_option
PATIENT_RELATIVES would have a field called pr_relative_name
APPOINTMENTS would have a field called ap_date
In a large schema you will often find two tables having similar names which could result in the same prefix. You can avoid this by thinking carefully about the name you give each table - and documenting the prefixes to be used.
One advantage of this prefix is that you are very unlikely to choose a reserved word by accident.
For very complex systems (thousands of tables) consider alternatives e.g. a prefix/suffix to identify the Application module.
Keeping names short: Oracle places no limit on the number of columns in a GROUP BY clause or the number of sort specifications in an ORDER BY clause. However, the sum of the sizes of all such expressions is limited to the size of an Oracle data block (specified by the initialization parameter DB_BLOCK_SIZE) minus some overhead.
Primary Key Fields - indicate by appending _pk
PATIENTS would have a primary key called pa_patient_id_pk
REGIONS would have a primary key called re_region_id_pk
And so on…the name of the primary key field being a singular version of the table name. Other tables containing this as a foreign key would omit the _PK
CLINIC_ATTENDANCE might then have a foreign key called ca_patient_id
or alternatively: ca_patient_id_fk
Tables with Compound PK's, use _ck in place of _pk
Notice that where several tables use the same PK as part of a compound foreign key then the only unique part of the FK fieldname will be the table prefix.
View names are plural, field name is singular
View names should not contain spaces, words should be split_up_with_underscores.
While it is common to prefix (or suffix) all views with V_ or VW_, a strong argument can be made that neither are really needed.
Giving your Views friendly easy names will promote their use by developers and end-users, this in turn will mean fewer badly written queries and more use of 'shared SQL' which will improve the cache hit ratio.
For very large systems, it can make sense to prefix tables/views with the application module name, so a database holding data for both Widget Production and Human Resources data might prefix everything with either HR_ or WP_
Name the Primary Key index as idx_<TableName>_pk
PATIENTS would have a primary key index called idx_patients_pk
Name a Unique Index as idx_<TableName>_uk
PATIENTS would have a unique index called idx_patients_uk
Where more indexes are added to the same table, simply append a numeric:
Where ## is a simple number
PATIENTS would have additional indexes called idx_patients_01, idx_patients_02,…
Note - Conventions that attempt to use the column name as part of the index name become unmanageable as soon as you have multiple columns appearing in multiple indexes.
Primary and Unique constraints will be explicitly named.
Name the Primary Key Constraint as pk_<TableName>
PATIENTS would have a primary key index called pk_patients
Name a Foreign Key Constraint as fk_<TableName>
PATIENTS would have a Foreign Key constraint called fk_patients
Note - in general each constraint should have a similar name to the index used to support the constraint.
Without getting carried away, you can also apply a suffix to non key fields where this is helpful in describing the type of data being stored.
A column used to store boolean (Yes/No) values can be given a _yn suffix: retired_yn, superuser _yn, driver_yn
In lookup tables an easy way to identify the main text field is to name it as a singular version of the tablename:asset_types.at_asset_type_id_pk (Primary Key) asset_types.at_asset_type (Text field) asset_types.at_network_yn (boolean)
Type all SQL statements in lowercase, being consistent with capitalisation improves the caching of SQL statements. A common variant is to put only SQL keywords in capitals.SELECT em_employee_id_pk, em_employee, ab_start_date FROM employees em, absences ab WHERE absences.ab_employee_id=employees.em_employee_id_pk;
You already have a unique prefix worked out for every table, so use the same thing when an ALIAS is required - this makes the SQL much easier to read.
Always list tables in the FROM / WHERE clause in desired join order - even with CBO you are giving the Query Optimiser less work to do.
Prefix scalar variable names with v_
Prefix global variables (including host or bind variables) with g_
Prefix constants with c_
Prefix procedure or function call parameters (including sql*plus substitution parameters) with p_
Prefix record collections with r_ (alternatively suffix with _record)
Prefix %rowtype% collections with rt_ (alternatively suffix with _record_type)
Prefix pl/sql tables with t_ (alternatively suffix with _table)
Prefix table types with tt_ (alternatively suffix with _table_type)
Suffix cursors with _cursor
Prefix exceptions with e_
If a pl/sql variable is identical to the name of a column in the table Oracle will interpret the name as a column name.
Prefix package names with PKG_
Write one package for each table - named PKG_TABLENAME, put all other code that logically belongs to the schema, but not to any particular table in a single Schema package PKG_SCHEMANAME.
If, as is likely, more complex grants are required for different groups of users then create an additional package for each workgroup - these should contain no code just wrappers that call procedures/functions in the other packages.
This gives a level of separation between the basic code and the user security/grants and makes it easier to change one without breaking the other.
A pl/sql function name like PAYROLL.TAX_RATE the word PAYROLL could refer to either a schema or a package name.
If you apply a naming convention and then decide to rename something it is possible to use Edit-Replace to update the associated code. But consider these two fieldnames:area_codes.ac_code region_area_codes.rac_code
The columns are unique but one is a substring of the other!
Oracle database instance names are limited to eight characters. The first two or three characters of the name should reflect the Application, with the remainder indicating the nature of the instance.
e.g.Live instance SSLive Test instance SSTest Train instance SSTrain Data Warehouse SSdw Staging Area SSsa
Name Data files so that they identify the instance and the tablespace.
Each filename should end with a two digit numeric value starting with 01, that is incremented by 1 for each new datafile added to the tablespace.
Use the extension ".dbf"
e.g.SSLive_temp01.dbf SSLive_rbs01.dbf SSLive_clinical01.dbf SSLive_clinical02.dbf
Avoid naming tablespaces according to time periods.
(Oracle never forgets a tablespace and SMON will scan the list of tablespaces in TS$ every 5 minutes) For a partitioned datawarehouse, try to adopt a strategy of recycling the tablespace names.
The redo log is a separate file (not in the tablespace)
Name Redo Logs so that they identify the instance, group and member number of the log. Use the extension ".log"
For more detail on the physical placement of files see Oracle Optimal Flexible Architecture (OFA)
Lastly - write and maintain a data dictionary for all data elements - rather than just dumping the Oracle data dictionary into a text document or an Entity relationship diagram - you should also be defining the business meaning of each data item.
RDBMS naming conventions can become the subject of endless debate - here are a few last things to consider:
Does your naming convention make names longer or shorter?
PURCHASE_ORDER_DATE versus PO_DATE
Will you have novice users writing SQL against the database?
If so will they understand the meaning of things like PO_DATE
Is the naming convention documented somewhere that everyone can find? If you don’t plan to change it very often, drop the text right into a table SELECT * from Naming_Conventions;
“We are braver and wiser because they existed, those strong women and strong men... We are who we are because they were who they were. It's wise to know where you come from, who called your name” ~ Maya Angelou
Related Oracle Commands:
Big list of Naughty Strings - Strings which have a high probability of causing issues.
Optimal Flexible Architecture (OFA)
Ask Tom - Argues against the column prefix
Oracle-Base.com Naming conventions
launch by lunch - Naming Conventions
StackOverflow - Naming Conventions by PerformanceDBA
SQL formatting convention - Philip Greenspun
Reddick Naming convention (Access VBA)
SQL formatter - Online SQL beautifier
Standard ISO-11179 - Rules for defining data elements
IT System Documentation Levels