Naming Database Objects

Just as using an object and variable naming convention can help you write cleaner code, a database object naming convention can help you manage the many objects you create in a database schema. I've developed and refined a standard that has worked exceptionally well for me in my database applications. The following rules are applied to all identifiers and serve two basic objectives: Provide a reasonable degree of compatibility across various platforms. Eliminate tedious "nuisance work" delimiting identifiers with square brackets in code and SQL statements. Maximum Identifier Length: 30 Characters
This is based on the maximum length of identifiers for Microsoft SQL Server. Jet/Access databases can have much longer identifiers, but staying within the 30 character maximum is easy to accomplish and avoids naming problems if a database is scaled from Access to SQL Server. Only letters, numbers, and the underscore are allowed in names.
No spaces, punctuation, or extended characters are allowed. This provides compatibility with MS SQL Server and avoids the need to use square brackets as delimiters around identifier names in Visual Basic code and SQL statements. This provides considerable simplification when writing code and SQL statements will little or no loss in readability. A column name of CustFirst is just as easy to understand as Customer First. The first character in a name must be a letter.
This also provides compatibility with Visual Basic identifiers and eliminates the need to use square brackets. Use mixed case to delimit words where required.
The underscore can also be used to separate words in exceptional circumstances, although the use of mixed case is preferred. Use the shortest possible name without using pointless or obscure abbreviations.
Abbreviations should be avoided entirely if possible, and only used if the savings in the number of characters is significant. For example, "Itm" saves only one character in comparison to "Item" at a cost of loss of readability and added obscurity.
Be consistent in the use of abbreviations when used. For example, if you like to use "Num" for columns representing some type of count, then always use "Num" - don't use "Num" in one place and "Cnt" somewhere else.
Note: A good "rule of thumb" for identifier names is that you should be able to read them over the phone without needing to spell them out. Different database platforms, Oracle, Sybase, and so on may all impose their own restrictions on identifier names. You should develop your own standard to comply with the rules of your own platforms. In xBase compatible databases table names are limited not only by the database engine but by the file system as well. Column names are similarly restricted. For example, in Microsoft Visual FoxPro 5.0, column names for free tables are limited to 10 characters. Some of these engines (including FoxPro) do allow you to assign longer identifiers if the table is included in a data dictionary. Check the documentation for your database engine and develop your standards based on the rules it imposes. The following rules are applied for naming tables: Regular Tables
Tables are named using the plural form of the object they represent. This convention is similar to the Microsoft Consulting Services naming convention for collection classes. For example, if the table contains customer data, the name would be Customers. Junction Tables
Tables used as the junction of a many-to-many relationship use the name of both tables. For example, if a table is the junction between an Orders table and an Items table, the name would be OrdersItems. Extension Tables
Tables used as extensions to a base table in a one-to-one relationship are named using the base table name and a word describing the nature of the extension. For example, you may have a generic Persons table with basic name and address columns that has a one-to-one relationship with an extension for only a few individuals. If, for example, you have an extension table storing information specifically about people who are doctors, info for the doctors would be in the PersonsDoctors table. General rules for identifiers apply to all table names. Microsoft Access databases use a prefix of "MSys" for database engine system tables and "USys" for user-defined system tables. Microsoft SQL Server databases use a prefix of "sys" for system tables. In order to eliminate naming collisions in queries with multi-table joins, a column name prefix is determined for each table. By prefixing column names with a few characters, every column name within the name space of a database will be unique. Use an abbreviated form of the table name for the prefix.
For example, the prefix for a Customers table would be "Cust". Each prefix must be unique within the database.
This guarantees that all columns names within the database will be unique. Special rules for junction tables.
For tables that act as the junction of a many-to-many relationship, you may use either the first letter of each table, or if that is not unique, use the prefix from each of the other tables. For example, the prefix for a table named OrdersItems could be either "OI" or "OrdItem". Counter/Identity Columns
Columns with autoincrement values used as the primary key column for a table are named with the table prefix followed by "ID". For example, the primary key column for the Customers table would be CustID. All Other Columns
Use the table prefix followed by a noun describing the attribute the column represents. For a Customers table, column names would include CustFirst, CustLast, CustCity, CustState, etc. General rules for identifiers apply to all column names. The column name prefixing concept is not a standard convention in the industry, but I've found that it works well, is easily understood, and avoids a variety of problems when building queries with multi-table joins. The following rules are applied for names of indexes and contraints: Primary Key
Use "apk" followed by the name of the table. For example, the primary key for the Customers table would be apkCustomers. The table name is used because there can be only one primary key, so using the table name means you can determine the name of the index without knowing what columns are used.
Note: The "apk" prefix is used instead of the more obvious "pk" to provide compatibility with server tables attached to Jet/Access databases. When Jet attaches a remote server table, it assumes that the first index found alphabetically is the primary key. Using "apk" places this index first in an alphabetical list. Other Unique Indexes
Use "udx" followed by the names of the column or columns in the index. If, for example, you have a table of states in the U.S., you could use an autoincrement column as the primary key but define a unique index on the state name or the two character postal code (or both).
Note: Not all tables will have this kind of alternate key. Foreign Key Constraints
Use "fk" followed by the column name or names. Remember that foreign keys are defined in the table on the many side of a one-to-many relationship. For the case of a one-to-one relationship, define the foreign key on the table that is a subset of the data in the larger table.
Note: In Jet databases, foreign key constraints are called Relation objects and are managed using the relations collection. However, you can still define them using CREATE TABLE or ALTER TABLE with a CONSTRAINT clause. Clustered Indexes
Use "cdx" followed by the table name. Like a primary key, there can only be one clustered index in a table, so the table name is used based on the same reasoning as that for naming the primary key. All Other Indexes
Use "idx" followed by the name of the column or columns used in the index. General rules for identifiers apply to all indexes and constraints. Not all database engines, Jet included, support clustered indexes. If you are not familiar with clustered indexes, a clustered index is an index where the leaf pages are the actual data pages. In normal indexes, the leaf pages are pointers to data pages. If you use Microsoft Access to create your database and define relationships using the Access Relationships window, all of your foreign key constraints will have names like "Relation1", "Relation2", etc. Although it is rare to reference this type of index in code, having more useful names is helpful if you do. Most database engines do not create an index that can be used for query optimization when you define a foreign key constraint. You should add an additional index for performance reasons on any column used as a foreign key. Microsoft SQL Server and other server databases support several different types of constraints other than foreign key constraints. You can, for example, name defaults and check constraints (known as validation rules in Jet databases). Defaults
Use "def" followed by the column name. Check Constraints
Use "chk" followed by the name of the column or table, as appropriate. Rules for objects other than tables, columns, and indexes can be more difficult to define, although the general rules for identifiers are followed wherever possible. Queries
The simplest method for naming saved queries is to prefix query names based on the purpose of the query. For example, "rpt" for report record sources, "frm" for form record sources, etc. User Accounts
There are several commonly used formulas for generating user account names, but the most common is to use a comination of the names or initials of the user's name. For example, if the user's name is John Q. Public, several possibilities would be "jpublic", "johnp", and "jqp". Synchronizing database account names with network account names will simplify the life of the user by reducing the number of account names and passwords the user needs to remember.
Note: Some database systems, including Microsoft SQL Server, allow you to use security systems that are integrated with the network security system, making the management of user accounts easier for both administrators and users. Stored Procedures
The SQL Server convention is to use "sp" as a prefix to the procedure name. Extended stored procedures are prefixed with "xp". Triggers
In most cases there will be three types of triggers available: insert, update, and delete. Trigger names use a combination of either "i", "u", or "d" to represent the type of trigger and the table name. If the trigger is used for more than one operation, the appropriate characters are combined.
Note: Jet databases do not support triggers. Just as is the case with variable and object naming conventions in Visual Basic code, the particular convention you choose is less important than developing or adopting a convention and following it faithfully. Rather than thinking of a naming convention as yet another complex set of rules to follow, it is helpful to think of a naming convention as a means of simplifying the task of database design and development. A well thought-out naming convention makes your life as a developer easier by eliminating one area of decision making (or at least consolidating that decision making process into the process of developing the naming convention) and making the design more consistent by applying a set of standards to at least one area.

Originally written by Joe Garrick

View the original article here

VB6 Naming Conventions

A good naming conventions for objects you use in your code can go a long toward making the code easier to read and maintain. Here's what I'll be covering: IntroductionChoosing Identifier NamesNaming ProceduresNaming VariablesModifier TablesSummaryI'm going to give you a little warning right from the start. The use of naming conventions is a religious issue for many programmers - sometimes ranking as high as the age-old goto debate or the choice of language. Some like naming conventions and some hate them. Most large programming organizations, however, do use them. I also think that many of those that passionately hate having naming conventions imposed on their work would probably (perhaps grudgingly) admit their usefulness.

Like many other "standards", what's more important than any particular naming convention, is that you choose a naming convention and use it faithfully. While you can probably get away with using your kids names for variable names in a 100 line program, you're inviting chaos if you take that approach with any serious application.

I will state one thing as a rule up front. Use Option Explicit. If you're new to Visual Basic and not familiar with it - its a feature that requires you to declare each variable in your program. Without Option Explicit, every time you type a new variable name, VB will create a new variable for you. As you can imagine, this can create bugs that are extrememly difficult to find if you happen to mistype a variable name somewhere. Other than pure laziness, there's simply no excuse for not using Option Explicit, so I have absolutely no sympathy for you if you ever have a bug caused by letting the compiler create a variable you didn't intend to create.

In the rest of this page, I'll describe the standards I've used for naming procedures, variables, and other objects of various types. Feel free to use my convention or invent your own.

Most of the papers and books I've read that cover naming conventions spend a lot of time covering things like prefixes for different data types, etc., and neglect to cover the most basic concept - creating a good name for an identifier. Sure, it can help to use variable data type prefixes, but it doesn't help much if the name of your loop counter is intBunny and the procedure that contains the loop is called strDoSomething.

If you've done a decent job designing the application and the code, it doesn't take much effort to come up with decent names for the identifiers. The only problem that you're likely to run into is that the names may become excessively long. While very long names can be tiresome to use, its my opinion that you're better off to take the time to type an extra character or two than to use a meaningless or hard to interpret name.

Let's take a look at an example of how using appropriate variable names can help you write better code. Have you ever seen something like this: For i = 0 To 10 For j = 1 To 100 List1.AddItem x(j, i) NextNextIts more likely than not that in this case the array indexes have been reversed inside the inner loop. The programmer probably meant to do this: List1.AddItem x(i, j)Its easy to eliminate this problem before its created by giving the loop indexes more meaningful names: For iDivisionIdx = 1 To iDivisionCount For iDistrictIdx = 1 To iDistrictCount lstCustomers.AddItem sCustNames(iDivisionIdx, iDistrictIdx) Next ' iDistrictIdxNext ' iDivisionIdxThere's no confusion now over what loop index we're dealing with. Not only has the problem of transposing the indexes been elimiated - the entire loop construct is easier to read and understand because the names reflect the data being used.

Here are a few things to consider when naming identifiers in general: Use a name that represents the data or the function.
The name chosen should represent the data being used. If you are creating a variable that represents the total number of employees, EmployeeTotal or EmployeeCount would be good names. Use abbreviations consistently.
While its not necessary to have variable names that are 30 characters long, don't go overboard in abbreviating names. "Tm", for example, is only two characters shorter than "Time" and much less readable. There some common tags that are often used, such as Num for Number, Idx for Index, and so on. Feel free to use these if you wish, but if you do, use them consistently. Don't use Num some of the time and Number or Count at other times. Make the name readable.
A good rule to apply here is that you should be able to read your code to someone over the phone and have them be able to write it down without asking you for spellings. Use a noun for variables.
Pick a word that represents the data. This will make the code somewhat self-documenting and considerably easier to read. Use a noun and a verb for procedures.
If a procedure performs an action on an object, use a verb describing the action and a noun describing the object. For example, if a procedure adds a new contact to a database, "AddContact" might be an appropriate name. Use caution with temporary variables.
This is an area that can often cause problems such as the loop index error shown earlier. You may often need a temporary variable, such as a loop index or dummy variable to hold a return value from a function that you don't need. However, consider this: There's really no difference between a temporary variable and any other type of variable. They are all temporary since they only exist in the memory of the computer while the program is running and are always an abstraction created for the benefit of the programmer. The computer doesn't care what name you use, so why not take the time to type an extra character or two and use a meaningful name. In a large, complex program, you may have hundreds or thousands of procedures. Using a naming convention and good identifiers can make your job of finding and using the procedures considerably easier.

One standard that is commonly used for procedure names is to use a verb describing the action performed followed by a noun describing the object acted upon. I have to say that I'm not particularly fond of this convention, mainly because it makes it difficult to find a procedure in an alphabetical listing. If you are using "Get" as a prefix to functions which return values (such as GetWindowText), you'll end up with a long list of GetThis and GetThat procedures. I've always thought that it makes more sense to use the same technique but to reverse the noun and the verb. GetWindowText would become WindowTextGet. This groups all the procedures associated with a particular object together. Unfortunately (for me anyway), I'm probably the only person to have attempted to use such a convention - so make your own choice to be consistent with the rest of the world or make up your own standard. Perhaps something else entirely works best for you.

Regardless of the naming convention you choose, there are a few things which a procedure name should do for you: Use a name that represents an object and an action.
Whether you use verb-noun or something else, the name of the procedure should indicate what the procedure does and what it does it to or with. Be consistent in the order of the words.
If you're going to use verb-noun, always use verb-noun. If you mix and match the two, you're only making your own life harder. While you may be able to remember all of the procedure names in a program with 100 procedures, you are unlikely to accomplish the same feat in a program with 1,000 or 10,000 procedures. Be consistent in the verbs and nouns used.
Just as you should be consistent when naming variables, you should also be consistent when naming procedures. If you're going to use SetFirstName, also use SetLastName rather than AssignLastName. There's one other thing I'll mention which crosses the line between a coding standard and a naming convention. A well written procedure should do one task. If it does this, it will be easy to create a name for it. On the other hand, if the procedure does six things to four different objects, you may end up with a name like "AddCustomerAndUpdateRepPhoneAndFaxNumber". If you end up with a procedure name that looks like a run-on sentence, you probably haven't written a good procedure. If you need a name like "DoABunchOfStuff", you've almost certainly written a poor procedure. Before fudging a half-baked name, take another look at the code. Clearly its important with variables, just as with procedures and all identifiers, to use a name that is readable and representative of the data in use. There is, however, a debate among programmers over the use of various means of identifying variables by data type and usage. Some programmers like to use prefixes to identify the data type of the variable, while others prefer to use type declaration characters, and still others use neither and omit data type identification from the name entirely. The closest thing you'll find to a "sanctioned" naming convention for variables can be found in the MSDN/Visual Basic Starter Kit on the VB4 Professional edition CD under Visual Basic Programming Conventions from Microsoft Consulting Services.

I happen to like using data type prefixes, although I don't follow the Microsoft standards in all cases. Here's why: The code is easier to read.
I find that type declaration characters distracting when reading a code listing and difficult to verbalize. You aren't bound to VB's data types.
If you wish to define "hWnd" as a prefix for window handles, you can do so without breaking the standard. You can design your own standard.
The convention published by Microsoft specifies using "int" as a prefix for integers. I happen to prefer using just "i" - it saves a fair amount of typing for one of the most frequently used data types without sacrificing readability. Some of the data types introduced in VB4 have no type declaration character.
While I don't expect VB to drop the type declaration characters entirely in future versions, I also don't expect type declaration characters to be supported for any future data types. Also, many of the variables you work with are object variables which also have no type declaration character. There are two other notable sets of tags I use for variable names.

The first is a prefix indicating the scope of the variable. I use "m" for module level variables and "g" for global variables. These tags not only identify the scope of the variable but also avoid name collisions and name shadowing between variables at different scopes.

The second is a prefix for parameters. I use this to avoid naming collisions between parameters and local variables and to distinguish between input and output variables. Input parameters are prefixed with "p" and output with "rp".

By using these additional modifiers, I can normally be sure that I never two variables in scope with the same name.

Here are most of the modifiers I use, grouped as follows: Data Type ModifiersScope ModifiersMiscellaneous ModifiersControl TagsDAO ObjectsThe data type modifiers are prefixed to the variable name. The scope modifiers are used before all other prefixes. Variant
This is used with normal data type prefixes to indicate that a variant is being used but a specific data type is expected. For example, in a database application a foreign key might normally be a long (l) but may also be Null, thus the use of a variant. The resulting prefix would be vl. Procedure Parameter
This prefix is used to help avoid name collisions when working with local variables and procedure parameters which otherwise share the same name. This modifier is prefixed to the base data type modifier. Return Parameter
Used to indicate output (return values) passed through the parameter list in a procedure. This modifier is prefixed to the base data type modifier. User Defined Type
This modifier is used to indicate that the variable is a user-defined data type. It precedes all but the scope modifier. Array
This modifier is appended to the variable name and is used to indicate that the variable is an array. Form
The single upper case F is used in form names as specified at design time. The frm tag is used for variables representing forms. For variables, this tag follows the scope modifier. These tags are used in control names and in variables representing controls. These tags are used with DAO objects and precede all but the scope modifier.

Given the number of available objects, custom controls, etc., that are available for use in Visual Basic, it would be impossible to come up with a comprehensive list of tags for every type of object. For those not listed (or maybe for those that are as well) you'll need to come up with your own tags. What's more important than using any particular set of tags is to choose a convention for the names and use it consistently. You're sure to be kicking yourself if you go back and look at your code after six months and discover that half of your Byte variables and half of your Boolean variables are prefixed with "b". That was a lot to take in, but don't let yourself be confused or intimidated by the number of different modifiers and rules I use in naming objects. Once you get used to using a convention like this it becomes second nature and really does make life easier by allowing you to recognize the nature of the object by merely reading the name.

I'll repeat this again - it doesn't really matter what particular naming convention you choose as long as you name objects consistently. There's enough to learn and do in writing an application without expending excess effort in deciphering poor object names. Using a naming convention will eventually become automatic and that ultimately saves effort because its one less thing to think about.

Note: I have also published a Database Object Naming Conventions page. You might find it helpfull.

Originally written by Joe Garrick

View the original article here