Naming standards or conventions

[Jennifer Murphy]

Are there any naming standards for database objects (tables, fields, etc)?



I did a search and came up with a lot of opinions, but no consensus. Some advocate prefixes like tbl, frm, qry. Others do not. Some say table names should be plural. Others are silent on that.

If there is a standard or a consensus, can someone provide a link?

If not, can anyone offer opinions and rationale?

For example, if I have a table of publishers, do I name it Publisher, Publishers, tblPublisher, tblPublishers, ???

In that table, should I name the primary key field, PublisherID, ID, ???

Thanks

[June7]

There is no standard as in hard and fast rules, there is best practice, common sense, and what makes sense to you. Do what helps you. Whatever you do, be consistent. For instance, prefixes are considered good practice but the exact form of the prefixes is personal preference. tbl, qry, frm, rpt are all commonly used.

Advise no spaces or special characters/punctuation (underscore is exception) in names nor reserved words as names.

I use prefixes for code variables and controls but never have established prefixes for objects (tables, queries, forms, reports) or fields.

I like plural.

[orange]

Here are some naming conventions from an experienced poster
http://www.access-programmers.co.uk/...1&d=1361707345

[June7]

Nice document. One thing I am not sure about - it says tables and queries are listed together in the 'query window'. They are not listed together in the query builder or relationships builder. Maybe something has changed since this document was created or I am looking at the wrong 'window'.

Shows examples of object names with spaces which I would not do.

[orange]

June, I think the author was saying that when you do to the query /builder wizard, you can select tables or queries (separate tabs).

I would not use spaces in names of tables or objects.

Here's a great link for anyone wanting to learn about database and access; it includes naming in section 3.3.
http://www.accessmvp.com/strive4peace/Index.htm

[June7]

Author said they were listed together but they aren't unless you select 'Both'. There is option to view them listed separately. A minor inconsistency in an otherwise fairly good summary. I think the use of spaces in the object names a rather significant flaw.

[ItsMe]

I use prefixes for code variables and controls but never have established prefixes for objects (tables, queries, forms, reports) or fields.

I am all about prefixes for objects like tables, etc. And I agree about no prefixes for field names. Save the prefixes for controls and variables. I stay away from underscores in names because I like to use them in error trapping while writing VBA.

[alansidman]

You may want to check this out also.

http://access.mvps.org/access/general/gen0012.htm

[Dal Jeanis]

Standards are great. There's so many of them to choose from.

Here's my personal suggestions.

1) Avoid making people type things that are stupid.

2) Make tables names as short and as meaningful as possible. When possible, name them after the entity that they actually represent.

3) Most Access programmers prefer CamelCase. If you must put extra characters between words in a name, use_underscores, not-hyphens and especially not spaces. Hyphens can be read as minus signs, making life interesting. Avoid making life interesting.

4) Avoid spaces in field or table names - they create the opportunity for massive headaches, and the requirement to type open and close braces [] all over the place.

5) Avoid reserved words, especially Time, Date, or any function name.

6) Put tbl on the front of tables, qry on the front of queries, and don't put either of those prefixes on the front of anything else.

7) Avoid allowing Microsoft to default the names of controls to be the same as the underlying field on the table or query. That default convention makes it less clear what object you are manipulating, the control or the field. The difference will seldom make or break your application, except when it does.

8) Similarly, Always Always Always make sure that the subform control on a form has a name different from the subform you expect to be loaded in that control. This distinction will OFTEN make or break your application, until you get burned enough to learn the distinction.

[Jennifer Murphy]

Thanks to everyone for their thoughtful input.

I have one more question regarding the names of table fields. Suppose I have three tables: tblVendors, tblProducts, and tblContacts. In each table, I have a primary key named VendorID, ProductID, and ContactID respectively. The next field is the name of the entity. Do I call them all "Name" or do I use VendorName, ProductName, & ContactName?

The former is simpler and shorter, but possibly confusing. The latter is clearer, but longer and a little bit more unwieldy.

What say you all?

[ItsMe]

In a table that has personal info for Employees, another table for Clients, another for Vendors; what I normally do is use a shortened version of the respective table name.

tblEmployees

EmpID
EmpFirstName
EmpLastName
EmpAddr1
EmpAddr2
EmpCity

tblCustomers
CustID
CustFirstName
CustLastName
CustAddr1
CustAddr2
CustCity