[AccessD] Table naming conventions

Shamil Salakhetdinov shamil at users.mns.ru
Thu Mar 2 16:37:18 CST 2006


John,

I just noted that your naming conventions are solid and systematic and based 
on LRNC - and that's "what doctor ordered".

<<<
I adapt where possible.
>>>
Either do I.

Shamil

----- Original Message ----- 
From: "John Colby" <jwcolby at ColbyConsulting.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Friday, March 03, 2006 12:13 AM
Subject: Re: [AccessD] Table naming conventions


>I started with LRNS, and still use it for variables etc.  I do not claim to
> be the originator of anything, I adapt where possible.
>
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
> Salakhetdinov
> Sent: Thursday, March 02, 2006 8:11 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Table naming conventions
>
> John,
>
> AFAIS your naming conventions are based on LRNC (see e.g. Stan Leszinsky 
> "MS
> Access 97 Expert Solutions").
> One exception are field names - the recommended in LRNC way is to not use
> underscore and to use 3-4 chars standard abbreviations to prefix field
> names:
>
> - Company - comp
> - Person - pers
> - Employee - empl or emp
> ...
>
> I do use LRNC for the last 10 years. It  worked very well in many
> situations.
>
> Best regards,
> Shamil
> --
> Web: http://smsconsulting.spb.ru/shamil_s
>
> ----- Original Message -----
> From: "John Colby" <jwcolby at ColbyConsulting.com>
> To: "'Access Developers discussion and problem solving'"
> <accessd at databaseadvisors.com>
> Sent: Thursday, March 02, 2006 2:12 PM
> Subject: [AccessD] Table naming conventions
>
>
>> Folks,
>>
>> There has been a bit of discussion in the past few days re naming
>> conventions as applied to tables.  I have used conventions for many years
>> and I sat down tonight and started documenting the conventions I use.  I
>> have shared my conventions in the past and will do so again here.  I do
>> these things because they work for me, in my environment.
>>
>> I have built a framework, which I use in any project where it is allowed.
>> Having conventions allows me to make assumptions about how things will be
>> handled, and write code that used those assumptions to make decisions.
>> Even
>> if you do not use a framework, having objects defined in a consistent
>> manner
>> can just generally make life easier.
>>
>> Feel free to discuss the methods and assumptions that I make, add how you
>> do
>> things (and WHY) etc.  Don't bother calling the things I do stupid, if
>> they
>> don't work for you, don't use them.  Share what does work for you, and 
>> WHY
>> you do what you do.  If you do something exactly opposite of the way I do
>> it, explain why you prefer to do it that way.  I find rules useless.  I
>> find
>> reasons very useful.
>>
>> Table Definition Specification
>> Tables are defined in a consistent manner so that they can be recognized
>> and
>> worked with easily.  All of the definitions that I use have been
>> assimilated
>> from other people's ideas or developed over time, and all such 
>> conventions
>> exist to solve a problem, whether that be readability, distinguishing
>> functionality or allowing the framework to make assumptions.
>>
>> Table definitions consist of several parts.
>> 1. Tables always use an autonumber PK, whether it appears to need one
>> or not.  This is done for a variety of reasons, ranging from divorcing 
>> the
>> "table link" (PK/FK)from the unique index and making it trivial to change
>> that unique index without changing the "table link", to the consistency 
>> of
>> PK data type which allows specific assumptions in the framework.  The PK
>> is
>> always a long integer, and a single field, which easily allows searches
>> and
>> other programmatic manipulations that wouldn't be possible or would be
>> tedious if the PK was a multi-field set.
>> 2. Table naming conventions.  Tables are prefixed with 'tbl' so that as
>> table names  are encountered in forms, reports and queries they can be
>> distinguished from other objects such as queries.  Thus tblPerson,
>> tblCompany, tblVehicle.
>> 3. I have used other table prefixes to cause tables to group by type
>> and be visually distinguishable from each other.  Tbl for main tables,
>> tlkp
>> for lookup tables, tmm for many to many tables, ttmp for temporary tables
>> etc.  There is a lot of discussion about whether grouping by type is
>> preferable to grouping by functionality, i.e. all accounting tables group
>> together, all shipping tables group together etc.  That is very much a
>> 'preference' kind of thing.  I have done both, even within the same db.
>> It
>> is possible to throw a "type" suffix on the end of the tables separated 
>> by
>> an underscore if you care to group by function but be able to distinguish
>> table type.  tblPeopleVehicle_MM for a many to many, tblState_LKP for a
>> lookup.
>> 4. Field naming conventions.  Field naming conventions have several
>> parts.  In general field and table names should only change in the most
>> dire
>> circumstances.  If any object name changes, I have to do a search and
>> replace to find and fix them.  If there are a bunch of Fes working 
>> against
>
>> a
>> single BE, it becomes a nightmare.
>> a. Data type prefixes are never used in field names because of issues
>> with data type changes.  There are just too many data types, and they are
>> often changed well into a project, for very valid reasons.
>> b. A prefix to the field name is created using the first two characters
>> the table name where the table name is a single word - PEople, VEhicle,
>> COmpany.  In cases where the table name is multiple words, the first
>> character of each word is used.  InvoiceLineItem ILI, PeopleVehicle PV,
>> CompanyProperty CP.  An underscore is used to distinguish the field name
>> prefix from the rest of the field name.  PE_ID, PE_FName, PE_SSN.  Use of
>> the first two characters if each word is acceptable to avoid collisions
>> and
>> make the field prefix more readable.  PEopleVEhicle PEVE_ID.
>>
>> Obviously as a project gets large a table is required to track field name
>> prefixes already used, but for small to medium sized projects this allows
>> the developer to rapidly recognize which table a field comes from just
>> from
>> the field name.  If this naming convention is rigorously followed then
>> every
>> field in the db has a unique name, which allows "search and replace" type
>> of
>> operations to be performed without manual intervention.
>> c. IDs (PK/FKs) are distinguished by the capitalized characters 'ID'
>> in the field name.
>> d. PKs use the convention of Prefix characters, followed by an
>> underscore followed by ID.  PE_ID, VE_ID, CO_ID.
>> e. FKs use the convention of Prefix characters, followed by an
>> underscore, followed by ID followed by the prefix characters of the
>> foreign
>> table.  PV_IDPE, PV_IDVE. CP_IDCO.  By placing ID immediately following
>> the
>> underscore, PKs and FKs can be immediately distinguished from other types
>> of
>> fields.
>> f. PKs and FKs are grouped at the top of the field list in design view.
>> This is done so that the PK and all FKs can be seen at a glance without
>> having to search through the fields looking for PKs/FKs.
>>
>>
>> Thus field names look like:
>> tblVehicle:
>> VE_ID
>> VE_Color
>> VE_Make
>>
>> tblPeople
>> PE_ID
>> PE_LName
>> PE_FName
>> PE_SSN
>>
>> tblPeopleVehicle
>> PV_ID
>> PV_IDPE
>> PV_IDVE
>> PV_DateAssigned
>>
>> As you can see, by using the _ character it is easy to distinguish the
>> prefix from the rest of the name.  By using the ID immediately after the
>> underscore it is easy to find every PK/FK field.  By grouping all the
>> PKs/FKs at the top of the table, no searching is required to find the
>> PKs/FKs.  By using the _IDXXXX construct, it is easy to visually look at
>> the
>> FK and just "see" what table that is the PK in.
>>
>> As I said above, to me reasons are the important thing.  They define why 
>> I
>> do what I do and allow me to evaluate whether making a change to what I 
>> do
>> is useful to me.  Rules without the associated reason are useless since I
>> cannot evaluate how it applies to what I am trying to do.
>>
>> Rules are made to be broken, but I want to know the danger of doing so.
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>>
>> -- 
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com 




More information about the AccessD mailing list