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
No comments:
Post a Comment