[AccessD] Table naming conventions

Not read all this, but one point I want to make is "why are people obsessed
with PREFIXES?"

I use the "_tbl" SUFFIX in my table names. Why? So that when I sort the list
of tables I can use the initial letter of the table name to quickly jump to
it using the keyboard. If they all begin with "tbl" you cannot do that.
Simple. :-)

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Thursday, March 02, 2006 6:12 AM
To: 'Access Developers discussion and problem solving'
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