[AccessD] Field Naming ( Just once I want to be ahead of JC )

John W. Colby jwcolby at colbyconsulting.com
Wed Jun 22 11:56:23 CDT 2005


Good job Joe, my point exactly.

I have seen (and in fact am currently working on) too many tables where the
PK is just called PK or ID or something silly like that.  In the case I am
working on are named something real (TherapyLocationID) but that same field
name is used in several different tables, both as the PK and the FK in the
child tables.

The problem becomes when you try to search on "where is this field used",
without a table prefix in the field name, you get hits from every table
where that field name is used.  I currently am trying to straighten out a
mess where one table has the field TherapyLocationID as a FK. And it needs
to be removed entirely.  The therapy location is NOT one to one, thus there
is now a table that says where the therapy occurs, but in order to find
it... Well... Let's just say there about 100 hits only about 20 of which
have anything to do with the field in the table I need to modify.  I have to
search through all 100 of these forms, reports, modules etc to visually look
at which table that object is referencing when it uses TherapyLocationID.

Can you say PITA?

If all tables had a table prefix, this would not be an issue.

I personally use the first two characters of the table name if the name is a
single word:

Tbl child = 

CH_ID
CH_Lname
CH_Fname

Etc

tblPeople = 

PE_ID
PE_Lname
PE_Fname

Etc.

I use the first letter of each word if the name consists of more than one
word.  

tblInvoiceLineItem - 
ILI_ID
ILI_IDIN	(the FK holding the PK of the INvoice table

Doing this ensures that if I search for PE_ID, I will get ONLY hits from the
PEople table.  If I search for any field with ILI_XXXX I will get only hits
for items referencing the InvoiceLineItem table.

Notice that I then name all FKS with YY_IDXXXX

ILI_IDIN for example

In essence I take the PK name (IN_ID), remove the underscore and reverse the
ID and name part (IDIN) and place it immediately after the underscore
(ILI_IDIN).  

Why do I do this?  Because I push all IDs to the top of the table and then
all FKs look like

YY_IDAAA
YY_IDBB
YY_IDCCCC
YY_IDEEE

Etc.

ALL fields with _IDXXXX are an FK.

Just some things to think about.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Joe Hecht
Sent: Wednesday, June 22, 2005 12:37 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Field Naming ( Just once I want to be ahead of JC )


When naming fields in table design view I try to prefix the field with
something to identify the source table i.e.:
 
Custfname
custLname
 
 
repfname
replname
 
Joe Hecht
Los Angeles CA
 
-- 
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