Home • About • Contact
Login • Page Content • Site Map

Search
GH.com   GH.com dir
Wikipedia   Web

• Intro
• All Database Objects
• Databases
• Tables
• Columns
• Indexes
• Views and Queries
• Stored Procedures
• User Defined Functions
• Triggers
• Constraints, Defaults, and Rules
• SQL Statement Variables
• Backup Files
• Users and Logins
• Bibliography

Intro

The main reason to adopt a naming convention is so you and others can easily understand the structure. Naming conventions should produce concise, legible, unambiguous, and consistent names. Naming conventions should clarify instead of confuse; the strictest adherence to naming conventions will result in ridiculous names.

Who should adopt a naming convention? The following should:

• Ideally the whole industry should adapt a universal naming convention but that only happens on a high, intuitive level.
• It would be nice if users of particular platforms or products adopted a consistent naming convention but that usually doesn't go much further than what is absolutely necessary.
• It's quite possible for a company or department to adopt a naming convention. By analogy the conventions of The Wall Street Journal is quite distinct from the conventions of Rolling Stone , and yet each is consistent with their own conventions.
• At the very least an individual should use a naming convention so they can read their own material!

It is common to have names composed of different parts. Here are some common syntaxes:

[prefixes]{tag}[BaseName][Qualifiers]

[tagprefixes][tag][PREFIXES_]{BaseName}[Qualifiers][_SUFFIXES]

All Database Objects

Limit the length. Shorter is better. Here are actual DBMS maximum lengths for column names:

• Access: 64.
• MySQL: 64
• SQL Server: 128 or nvarchar(128) to be precise.

Use only ASCII letters, numbers, and underscore (_). The first character should be a letter since some DBMSs don't accept anything else as the first letter. Here are some details of the actual DBMS character restrictions:

• Access: Alphanumeric and most special characters except for: ., !, `, [, and ].
• MySQL: Alphanumeric (windows 1252), _, and $. First letter could be a digit (but why bother?). Last character cannot be a space (but why bother?).
• SQL Server: Unicode 2.0 letters, _, @, or #. If the name includes other characters then it must be delimited. No spaces.

Avoid abbreviations. If an abbreviation must be used, then avoid ambiguous or inconsistent abbreviations, and try to use the same abbreviation throughout the databases. EG:

• Does "pro" stand for "professional", "procedure", or "process"?
• Do you abbreviate "Global" as "G", "Glob", "Glbl", or "Globl"?

Here are some pretty common database abbreviations from the University of Arizona:

Name	Abbreviation	Description
Amount	Amt	A monetary value.
Category	Catgy	A specifically defined division or subset in a system of classification in which all items share the same concept of taxonomy.
Code	Cd	A combination of one or more numbers, letters, or special characters which is submitted for a specific meaning (includes Status and Abbreviation).
Date	Dt	The notion of a specific period of time.
Description	Descn	A word or phrase which interprets an abbreviation, code or other shortened term so as to make it more understandable to users (i.e., Arizona is the description of the abbreviation AZ).
Identifier	Idfyr	A combination of one or more integers, letters, special characters which designate a specific object/entity, but has no readily definable meaning (includes Designator, Key, Number).
Indicator	Indcr	An identifier which has a domain of only 2 states, such as: Y/N, ON/OFF,TRUE/FALSE, 0/1, etc.
Name	Nm	The designation of an object/entity expressed in a word or phrase.
Number	Nbr	A value which is not for the purpose of measuring a quantity or serving as a counter, but which is usually a numeric value (it may contain non-numeric characters, such as a Product Number, PO Number, etc.). It is distinguishable in that arithmetic operations are not usually carried out on it.
Quantity	Qty	A non-monetary value. (Includes: Average, Balance, Deviation, Factor, Index, Level, Mean, Median)
Rate	Rt	A quantity, amount or degree of something in relation to units of something else. (Includes: Factor, Frequency, Percent, Scale)
Text	Txt	An unformatted character string, generally in the form of words. (Includes: Abbreviation, Comments)
Time	Tm	A notion of a specified chronological point within a period.

Make names readable. A rule of thumb is that you should be able to read the name to someone over the phone without having to spell out portions. Note that too many abbreviations violates this rule.

Use mixed case, especially to distinguish between "words" within a part of a name. EG: ProductName. Some folks like to use underscore (_) to separate parts of a name. EG: HR_Status and HR_Ballots. I personally find that practice annoying. Plus there are more creative uses for the underscore (see Tables below).

It has been suggested by the Leszynski and Reddick to use the following prefixes to indicate the status of certain database objects. Note that z is used to make the items appear at the top of lists. I suggest adding the developer's initials or name after each of these prefixes so we know who to contact about the status of these items.

• zz denotes objects you've deserted but may want to keep in the database for awhile for future reference or use. EG: zzvueFoo.
• zt denotes temporary objects. EG: ztvueFoo.
• zs denotes system objects. EG: zsvueFoo. System objects are items that are part of the development and maintenance of an application not used by end users, such as error logs, development notes, documentation routines, relationship information, and so on.
• _ denotes objects under development. EG: _vueFoo. An underscore before an object name sorts to the top of the database container to visually remind you that it needs attention. Remove the underscore when the object is ready to use and it will sort normally. Note that I prefer to use a_, aa, or a0 to stick with the rule of thumb of using a letter as the first character.

Use nouns for most objects. EG: vueEmployee. Use verbs (EG: Multiply()) or verb and noun combinations (EG: AddDog()) for objects that do things such as functions or stored procedures. If you use verb and noun combinations, be consistent with the order, i.e. use NounVerb() or VerbNoun(). The latter is closer to English and is good for sorting a list according to the action taken, but the former is better for sorting a list of functions according to the object they work on.

This shouldn't need to be said, but avoid naming anything with a reserved word.

Databases

• Some folks like to prefix all database names with. EG: dbFinance. This automatically avoids reserved words.
• Prefix related databases with a common prefix. EG: ILCounties and ILCities for the Illinois databases.

Tables

• Kernel tables have singular or plural names. Some people prefer plural since a table represents multiple instances of that kind of entity. EG: There are many products (rows) in the Products table. I prefer singular because singular is usually shorter and it sounds better when you qualify columns. EG: Product.Name='cup' sounds better than Products.Name='cup'.
• Bridge tables (aka junction, middle, associative, or connector tables) are used in many-to-many relationships. Make a bridge table name by concatenating the names of the other two tables. EG: OrderProduct. Some folks like to use and underscore to emphasize the bridge. EG: Order_Product. This can produce really long names so you may want to follow the convention where the name of a join table is composed of abbreviations of the tables it joins. EG: OrdPro or Ord_Pro. It is also a good to consistently concatenate the table in bridge tables in alphabetical order. EG: Order_Product instead of Product_Order. If the bridge table bridges many tables, then it give its own custom name. EG: Crossroad.
• Extension tables have a one-to-one relationship with a kernel table. These are prefixed with its kernel table. EG:. Product_New.
• Look up tables have a look up key (which is often the primary key of the table) that is used to match one or more foreign keys in a foreign table. A table may act as both a foreign and a look up table. EG: Product is a look up table to the ProductOrder table, but Product is a foreign table to the Supplier table. Because of this, only dedicated reference tables (look up tables that change infrequently) can be given a special prefix. EG: ref or LU or zref_ or ZLU, as in  zrefUSStates.
• It is common practice to substitute the full foreign table name with an abbreviated version that is consistently applied throughout the database whenever that table is referenced in another name. EGs: Product.Pr_ID or Order.Pr_ID. Some people actually prefer to use the abbreviated version of the table name for the table name itself, especially if their DBMS only allow shorter names. EG: Pr.Pr_ID. Abbreviations provide brevity but they also tend to form code that looks very much like gibberish.
• Some people do not tag tables with type (EG: t, tbl, or tbl_) because tables are clearly identifiable as tables from context: they follow the FROM keyword or are used to qualify columns. However such a prefix has some uses:
  • It helps when you are doing a search and replace in code, because a table name without the tag may be a common word used in comments and such.
  • It may help you distinguish your tables from, say, system tables.
  • It automatically avoids using reserved words.
• It may be worth categorizing table adding these modifiers:
  • Relation to some business or organizational entity. EG:  HR_ and SL_ for Human Resources and Sales.
  • Associated application. EG: appRL_ for the Rush Licensing application.
  • Time frame. EG: y1999_ or _y1999.
• It is a good practice prefix temporary tables with something like temp because otherwise you'll inevitably forget that it was supposed to be a temporary table.

Columns

• Primary Keys
  • If the primary key of a table is composed of one column, then name it TableID or ID. EG: OrganizationID or ID. Do not use ID if the column is not an integer or the field has a common industry name. EG: SSN.
  • Primary keys may be suffixed with _PK, especially if the primary key is composed of multiple columns. EG: Car.Make_PK and Car.Model_PK. I personally prefer to make give each table with a single-column primary key and avoid the use of _PK.
• Foreign Keys
  • If the column is a foreign key, then it's name has this syntax: ForeignTableID or ForeignTableID_FK. EG: ProductOrder.ProductID or  ProductOrder.ProductID_FK is a qualified foreign key in the ProductOrder table for the foreign table Product. I personally don's use the _FK suffix since a column name that isn't the primary key but ends in ID is most likely a foreign key.
  • It is also common practice to substitute the full foreign table name with an abbreviated version that is consistently applied throughout the database. EG: PrOr.PrID or PrOr.PrID_FK.
• Order of columns in a table
  1. Primary key(s). EG: OrganizationID.
  2. A column like Name or TableName if applicable. For a table of people, it may be worthwhile to have Name in addition to FirstName and LastName.
  3. Foreign keys. Sometimes the foreign keys are "scattered" if each foreign key also has some accompanying columns. Too much of this sort of thing is usually an indication of tables that are functioning more like flat files.
  4. Misc. columns.
  5. Columns that you have repeated in other tables. EGs: Note, DateActivated, DateDeactivated.
• Miscellany
  • There is a debate about the usefulness of prefixing all column names with the table they belong to. If the table prefix is used, then I would certainly use the short ALL CAP version mentioned above. Here is the syntax: {LookUpTable_}{ForeignTable_}{ForeignKey}_FK.
    • EG: If the ProductOrder table has a foreign key that ties to the Product table, then here are some possible names.
      • If each column name was prefixed with table name:
        • ProductOrder.ProductOrder_ProductID_FK
        • ProductOrder.ProductOrder_ProductID
        • ProductOrder.ProductOrder_PrID_FK
        • ProductOrder.ProductOrder_PrID
        • ProductOrder.PrOr_ProductID_FK
        • ProductOrder.PrOr_ProductID
        • ProductOrder.PrOr_PrID_FK
        • ProductOrder.PrOr_PrID
      • If each column name was not prefixed with table name:
        • ProductOrder.ProductID_FK
        • ProductOrder.ProductID
        • ProductOrder.PrID_FK
        • ProductOrder.PrID
    • Table name prefixes on column names make column names unique. This is useful when working with SQL statements that involve multiple tables --you might be able to avoid qualifying column names with table. On the other hand, unless you do a join, the column name is unique in that table --and if you do do a join, then it is just as easy to qualify column names with the table name or alias of the table name.
    • The biggest complaint about this convention is that it adds a considerable amount of tediousness to writing SQL statements, esp. because of all the underscores.
  • Most people don't bother tagging columns with type (EG: c or col) since, they are clearly identifiable as columns from context.
  • Optionally tag columns with data type. EG: strDescription. This may be useful but can also be tedious and you should always know for certain the specifics of the data type of a column anyway. See SQL Statement Variables for commonly used tags indicating data type.
  • Optionally prefix columns with content type. EGs: date (for date), time (for time), dtm or stamp (for date and time), pub (for public notes), etc.

Indexes

• Indexes are always associated with a table or view, so it would be nice to have them ordered by table. Here is one possible syntax: {TableIndexed}{ColumnsIndexed}_IDX[p][u][c], where p is primary key, u is unique, and c is clustered. EG: ProductName_IDXc, indicates that this is a clustered index on the Name column of the Product table.

Views and Queries

• Views (SQL Server parlance) and queries (Access parlance) are usually complex SQL statements that should have its own custom name.
• It is helpful to tag views and queries with type since these may need to be distinguished from tables. I suggest using v, view, or vue for views and q or qry for queries.
• It is definitely worth categorizing views and columns by prefixing them:
  • With function. EG: flt for filter or lkp for look up. There are many suggestions for this that can be standardized across companies:
    • The Leszynski and Reddick list:
      • sel for select
      • app for append
      • xtb for crosstab
      • ddl for data definition
      • del for delete
      • flt for filter
      • lkp for look up
      • mak for make table
      • spt for pass through (don't ask me how that's derived)
      • uni for union
    • The Scott Reber list:
      • sys for system
      • app for application
      • mas for master
      • val for validation
      • dat for data entry
      • trx for transaction
      • his for history
      • qry for query
      • rpt for report
      • exp for export
      • imp for import
      • chg for changed
      • new for new
      • old for old
      • bac for backup
      • usr for user
  • Relation to some business or organizational entity. EG:  HR_ and SL_ for Human Resources and Sales.

Stored Procedures

• Stored procedures names should be verbs or verb and noun combinations. EG: INSCustomerInfo or CustomerInfo_INS. A  very similar idea is to use the primary table affected combined with the general function. EG: CustomerInsert.
• Some people don't tag stored procedures with type (EG: sp, sp_, stp, s, p, or proc) because these are always clearly identifiable from context: they follow the EXECUTE keyword. Avoid the sp_, dt_, and xp_ tags in particular because they are used by SQL Server and it also makes SQL Server look in the master database first. However a prefix is convenient if you are discussing a stored procedure in comments.
• It is definitely worth categorizing views and columns by adding these modifiers:
  • With function. EG: INS for insert or ARC for Archive.
  • Relation to some business or organizational entity. EG:  HR_ and SL_ for Human Resources and Sales.
  • Associated application. EG: appRL_ for the Rush Licensing application.
  • Stored procedures used in report. EG: r or rpt prefixes.

User Defined Functions

• User defined functions names should be verbs or verb and noun combinations. EG: GetConeVolume or ConeVolumeGet.
• It may be worth prefixing functions with type. EG: F, f,  fun, or fnc. It often easy to identify a function from context. Avoid using fn_ because that is how SQL Server prefixes its functions.
• Don't prefix functions with scope since out of scope functions have to be qualified anyway. EG: dbo.FRunMe() is local to the database where as master.dbo.FRunMe() is available in all databases.
• It is definitely worth categorizing user defined functions by adding these modifiers:
  • Data type returned. EG: str for string or tbl for table.
  • Relation to some business or organizational entity. EG:  HR_ and SL_ for Human Resources and Sales.
  • Associated application. EG: appRL for the Rush Licensing application.

Triggers

• Triggers are always associated with a table, so it would be nice to have them ordered by table. Here is one possible syntax: TableOfTrigger_TGR[i][u][d], where i is insert, u is update, and d is delete. EG: Product_TGRi, indicates that this trigger fires when an attempt is made to insert into the Product table.

Constraints, Defaults, and Rules

• Constraints and defaults in SQL Server (aka validation rules in Access) don't really need names since these should be implemented with CREATE or ALTER. Rules are outdated —constraints should be used instead.
• For the sake of backwards compatibility, it is still possible to create defaults and rules in SQL Server. If so, then these are always associated with a table, so it would be nice to have them ordered by table. Here is one possible syntax: TableOfDefault_[Description]_DEF for a default or TableOfRule_[Description]_RUL for a rule.

SQL Statement Variables

In SQL Server, variables follow this syntax:

• @VariableName. Local variables or parameters start with an @.
• @@VariableName. Global variables and some Transact-SQL functions start with a double-@.

It is worth while to prefix variable names with data type. Here is a table of common prefixes:

Access Data Type Prefix binary bin byte byt b counter lng currency cur date/time dtm dt double dbl flt hyperlink lnk integer int i w long lng dw l i memo mem OLE ole single sng sgl flt text str txt sz s yes/no ysn bln f

SQL Server Data Type Prefix char(n) & nchar(n) chr, nchr str s varchar(n) & nvarchar(n) vchr, nvchr str s text & ntext txt, ntxt datetime dtm dt smalldatetime sdtm dtm dt bit bit f binary(n) bin b varbinary(n) vbin bin b image img decimal[(p[,s])] dec num flt numeric[(p[,s])] num flt float[(n)] flt num real rel flt real bigint bint int i int int i smallint sint i tinyint tint int i money mny smallmoney smny mny cursor crsr sql_variant vnt v table tbl timestamp tmst tmstp uniqueidentifier guid user defined udt t

It may also be worth while to use these various name modifiers:

• p for parameter. EG: pintStart.
• rp for return parameter. EG: rpintFinish.
• a or_a for array. EG: astrNames.
• ia or_ai for an item in an array or collection. EG: strWinner = astrNames(iastrNames).

Backup Files

• The most common convention is to concatenate the database name, an underscore, and a time stamp. EG: dbFinance_200703070948.bak.

Users and Logins

• In MS SQL Server, have the database user names match up with the SQL Server login name. This makes for easier auditing.

Bibliography

Listed in no particular order are sources referenced while making this page:

• MSDN Library Online. Especially the reference sections on Access and SQL Server.
• Naming Conventions for Microsoft Access by Stan Leszynski and Greg Reddick. Revised 1994 May 25.
• http://members.tripod.com/~vyaskn/object_naming.htm by Narayana Vyas Kondreddi.
• http://www.citilink.com/~jgarrick/vbasic/database/naming.html by Jose Garrick.
• http://www.sqlsite.com/functional_data_modeling/ by Scott Reber.
• http://w3fp.arizona.edu/dataadmn/name_conv.htm.

Charles Simonyl is credited with the Hungarian naming convention of prefixing base names with lower case tags such as strObject.

Page Modified: (Hand noted: 2007-10-06 05:57:36Z) (Auto noted: 2007-11-17 06:47:55Z)