Naming Convention for Databases

Essential Guidelines for Database Naming Conventions

Naming Convention


  1. use lowercase characters
    • eliminates question of proper case as well as errors related to case-sensitivity
    • speeds typing rate and accuracy
    • differentiates table and column names from uppercase SQL keywords

Note:- (Case Sensitivity:-The case sensitivity of the database depends on the operating system being installed. In Unix filesystems, filenames are usually case-sensitive. Old Windows filesystems (VFAT, FAT32) are not case-sensitive (there cannot be a readme.txt and a Readme.txt in the same folder) but are case-preserving, i.e. remembering the case of the letters. The original FAT12 filesystem was case-insensitive.Current Windows file systems, like NTFS, are case-sensitive, that is you can have a readme.txt and a Readme.txt in the same folder, however, Windows disallows you to create a second file differing only in case.)

  1. separate words and prefixes with underlines, never use spaces
    • promotes readability (e.g. book_name vs. bookname)
    • avoid having to bracket names (e.g. [book name] or `book name` )(Yes [ ] and white space is a valid character on mySQL and Oracle, independent of the OS. but these are avoided because uniformity is not maintained)
    • offers greater platform independence
  2. avoid using numbers
    • may be a sign of poor normalization, hinting at the need for a many-to-many relationship
  3. Do not use dashes in database names.
  4. Do not start object names with a letter(Donot start object name with letter  i.e. with numeric  or underscore or punctuators)
  5. Keep names short but meaningful.  For example “SlQ” is too short. and “SalesFiguresForCompanyByFiscalQuarter” is too long.
  6. The name of the object should make it pretty obvious what type of data it contains, and if for some reason it does not, then there is always the metadata tables and/or the documentation you should have written when designing the system. Using datatype-style prefixes for columns like IContactID (integer) and VEmail (varchar) not only make the column names harder to read, they also make them less flexible.



For Example

  • Name of the project. Eg. nexty

  • Postfix date fields with ‘_on’. Eg. added_on
  • Primary key should be always ‘id’ (the primary key should always have postfix as ‘_id’ eg. table_id, book_id)
  • Reference table must be named in the format <First table><Second table> – Eg. PageTag
  • Foreign key must be in the format ‘<table>_<primary key>’ – all lower case – Eg. user_id

Table names

  1. choose short, unambiguous names, using minimum number of words
    • distinguish tables easily
    • facilitates the naming of unique column names as well as lookup and linking tables
  2. give tables singular names, never plural
    • promotes consistency with naming of primary key columns and lookup tables
    • ensures alphabetical ordering of a table before its lookup or linking tables
    • avoid confusion of english pluralization rules to make database programming easier (e.g. activity becomes activities, box becomes boxes, person becomes people, data remains data, etc.)
    • SQL statements read better, e.g. SELECT activity.name vs. SELECT activities.name
  3. avoid abbreviated, concatenated, or acronym-based names
    • promotes self-documenting design
    • easier for developer and non-developer to read and understand
  4. prefix lookup tables with the name of the table they relate to
    • groups related tables together (e.g. activity_status, activity_type, etc.)
    • prevents naming conflicts between generic lookup tables for different entities
  5. for a linking (or junction) table, concatenate the names of the two tables being linked in alphabetical order
    • orders linking table with a related entity table
    • expresses composite purpose of the table
    • this should be waived if the linking table has a natural, standard, or obvious name (e.g. “item” in: [order] 1 to M [item] M to 1 [product])
    • this must be waived if there are multiple linking tables between the same two tables, (e.g. “student” and “instructor” between the tables “person” and “class”)

Column names

  1. the primary key should be the singular form of the table name suffixed with “_id”
    • allows primary key to be deduced/recalled from the table name alone (e.g. primary key of the book table would be book_id)
    • consistent with the name of the foreign key
    • prevents having to alias primary keys in programming
  2. prefix the name of every column with the table name, excluding foreign keys
    • prevents using “name”, “order”, “percent”, etc. as column names and clashing with SQL/RDBMS reserved words
    • creates near unique column names (e.g. book_name, book_code, book_description, etc., often simplifying query design and SQL coding
    • makes the column names consistent with the primary key
    • differentiates foreign key columns from columns native to the table
    • maintains semantic transparency of column names when using table aliases (e.g. SELECT a.activity_name FROM activity a)
    • prevents naming a column the same name as the table
    • (this point would be useful for more clarification. eg the field name ‘code’ for table names ‘book’ and ‘author’ can be differentiated using this point as book_code and author_code. )

  1. foreign key columns should have the same name as the primary key to which they refer
    • makes the table to which they refer completely obvious
    • if there are multiple foreign keys referencing same table, prefix the foreign key column name with an appropriately descriptive adjective (e.g. lead_person_id, technical_person_id, etc. which transparently reference person_id in the person table)
  2. suffix date-type columns with “_on”, suffix datetime-type columns with “_at”, and prefix boolean-type columns with “is_” or “has_” (to differentiate these specific kinds of data from other types)
    • prevents confusing with more common text/number data types

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s