[[Dashboard|<< Back to Dashboard]]

{| align="right"
| __TOC__
|}

= Database Naming Conventions =
A naming convention is important when working with databases in order to provide a clear structure and format; especially in team environments.  By using these conventions the database can be understood by anyone applying these conventions; thus increasing maintainability. These are not rules but guidelines that can be adapted to any working environment.

== General Conventions ==

* All names should be in camelcase with the first letter in lower case.
<pre>
-- DO THIS --
users
permisions
userPermissions

-- NOT THIS --
Users
Permissions
User_Permissions
user_permissions
</pre>
* Separate name parts by using camel case and NOT underscores or spaces.  This provides better readibility and you will not have to use quotes when doing SQL statements.  Look at example in the previous point.
* Prefixes or namespaces are the ONLY parts of names that should be separated by underscores.  This defines a clear separation between names and areas.
<pre>
 -- DO THIS --
 blog_users
 blog_userPermissions
 
 -- NOT THIS --
 BlogUsers
 BlogUserPermissions
 Blog_UserPermissions
</pre>
* Do not use numbers in names.  This is poor design, indicating divided table structures.
* Do not use dot (.) separator between names, remember use camel casing.  This way you will avoid problems whend doing SQL statements as fields are accessed using dot notation.
* It goes without saying, do not use reserved database words in any name.
* Always try to use names that make sense and are descriptive of their purpose.
* Avoid abbreviations whenever possible.  ONLY use abbreviations that are well known and documented.
* Avoid acronyms whenever possible.  Only use acronyms that are well known and documented.  Also, they should all be in uppercase if used.
<pre>
-- DO THIS --
HTTPRequests
savedURL

-- NOT THIS --
http_requests
httprequests
savedurl
saved_url
</pre> 

== Table Conventions ==
* Tables are usually entity you are modeling for persistence.  So make sure the names are in proper English and carry natural meanings.  These names should make sense and should be descriptive.
* Avoid acronyms and abbreviations if at all possible.  If acronyms are used, then make sure to capitalize them.  Abbreviations used should be well known abbreviations and should be camel cased.
* Avoid using plural names for tables, use singular forms.  This will avoid errors due to pluralization of words and also when moving table design to objects or entities.  The name should map to the entity it is modeling.
<pre>
-- DO THIS --
blog_user
blog_page
person
box
activity

-- NOT THIS --
blog_users
blog_pages
people
boxes
activities
</pre>
* Use namespaces for tables whenever grouping is needed.  This grouping is a clear separation when working with the tables and can also provide a good basis for security, as you could secure via the namespace.  Namespaces should be separated by an underscore and then the name should be camel cased.
<pre>
-- DO THIS --
lookups_countries
lookups_states
lookups_regionalOffices
hr_employees
hr_salaries
is_employees
is_vacationDays

-- NOT THIS --
LookupsCountries
lookupsStates
LOOKUPS_RegionalOffices
HREmployees
ISEmployees
ISVacationDays
</pre>
* Do not use prefixes like ''tbl,db'' as they are EXTREMELY redundant and useless.
* All tables should have at least ONE primary key.
 
 
== Column Conventions ==
* Columns are usually attributes of an entity you are modeling via a table.  So make sure the names are in proper English and carry natural meanings.  These names should make sense and should be descriptive.
* Avoid acronyms and abbreviations if at all possible.  If acronyms are used, then make sure to capitalize them.  Abbreviations used should be well known abbreviations and should be camel cased.
* Get used to naming primary or foreign keys with an ''ID'' identifier suffix. DO NOT use the column name ''id'' to identify a primary key.  Be more descriptive, usually looking at the table name.
<pre>
-- DO THIS --
userID
userPermissionID

-- NOT THIS --
id
userid
upid
</pre>
* Foreign keys should be prefixed with a capitalized ''FK_'' in order to visually denote relationships and also to avoid ambigous table selects.  After the ''FK_'' the name of the column must comply with our naming conventions.
<pre>
-- DO THIS --
FK_userID

-- NOT THIS --
fkuserid
userID
</pre>
* Boolean columns should use the ''is'' prefix.
<pre>
-- DO THIS --
isActive
isSold

-- NOT THIS --
active
sold
</pre>
* Date columns should be suffixed in camel case with the word ''Date''.
<pre>
-- DO THIS --
createdDate
updatedDate

-- NOT THIS --
date_created
date_updated
</pre>

* Try to make 

== Index Conventions ==
* Always prefix all index names with ''idx_'' to distinguish it as an index.
* The names of the index should at least convey the columns it reaches or at least the purpose of the index.
 
== Views Convention ==
* Always try to use names that make sense and are descriptive for the views.  Avoid acronyms and abbreviations if at all possible.  If acronyms are used, then make sure to capitalize them.  Abbreviations used should be well known abbreviations and should be camel cased. 
* All views should be prefixed with ''vw_'' in order to distinguish them in SQL statements from regular database tables.

== Related Guides ==
* [[MVC | Model-View-Controller Demystified]]
* [[DevelopmentBestPractices | ColdFusion Development Best Practices]]
* [[SQLInjectionBestPractices | ColdFusion SQL Injection Protection Best Practices]]