[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