[AccessD] Table naming conventions

Charlotte Foust cfoust at infostatsystems.com
Thu Mar 2 10:03:21 CST 2006


Shamil,

I, too, have used Stan's naming convention and have since it was
originally introduced as LRNC (R went off and developed his own later
;o>).  The underscore annoys me because it is harder to type than just a
capital letter, which is, I believe, the reason it was discouraged in
LRNC.  I really think it boils down to whatever works for the developer,
but some kind of naming convention is critical.  I've worked with some
of John's tables (bet you didn't know that, did you, JC?) and I
absolutely HATE his naming convention.  I couldn't use it in a million
years and I break out in hives just thinking of it, but it works for
him.  It only becomes an issue in a team development environment, where
using the same or at least compatible naming conventions is critical.

Charlotte Foust


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil
Salakhetdinov
Sent: Thursday, March 02, 2006 5: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



More information about the AccessD mailing list