Field names

  • Thread starter Thread starter Tom Snider-Lotz
  • Start date Start date
T

Tom Snider-Lotz

I'm looking for an efficient way to name fields that are linked betwee
tables, so that it's easy to tell which table the field resides in.
For example, there might be a Customer field in the Customers table an
the Orders table. When you're picking fields from a list, sometimes i
matters which Customer field you pick.

What I've done so far is to add the table name to the fields name, a
in "Customer_Customers" and "Customer_Orders." That works, but it'
kind of cumbersome. Does anyone have a better method? Or, if yo
don't bother with it, how do you make sure you pick the right field?

Thanks in advance.

-- Tom Snider-Lot
 
Suggest that you set up short descriptive field names in each table. Key
fields should be named alike; i.e., primary key (PK) fields in one table
have the same name as foreign key (FK) fields in another table. Other than
that, however, you should not have the same field names used multiple times.
You may have table normalization problems if this is what you're doing.

1) Set up tables.
2) Set up relationships (tools menu) to enforce referential integrity.
3) Set up queries / forms / subforms based on the tables.
 
It could be most anything where I'm picking fields from a list, sa
using a wizard or picking from available fields for a report. Bu
let's say for example I'm using a wizard to pick fields from a query t
use in a combo box. I might want to know which table a certain fiel
comes from.

In general, it seems inelegant for fields in different tables to hav
the same name
 
The naming convention that I consistently have been using over that past
couple years is to have each field in a table begin with 3 letters that
describe the table name. For instance:
tblEmployees
==============
empEmpID 'autonumber primary key
empFirstName
empLastName
empDOB
emp....
Every table has an autonumber primary key that consists of the 3 letters
duplicated followed by ID. The same field value in another table might be
like:
tblWorkSchedule.wksEmpID
or
tblTimeOff.tmoEmpID

I always know which table a field comes from as well as the primary key. I
always reserve "******ID" for numeric fields that are either primary or
foreign keys.

I have never wished I had done this any other way. Tony Toews suggests
something similar at http://www.granite.ab.ca/access/tablefieldnaming.htm.
 
Hi,

I use singular for fields (columns) and plural for tables. So, I would
use Customer for column name in both table, if they are related to each
other (probably through a Data Referential Integrity). If you use a query
where both tables are implied, you HAVE TO differentiate the origin of the
fields anyhow, with the syntax

Customers.Customer
or
Orders.Customer

Otherwise, a customer is a customer, thanks to the DRI, just don't use
the SAME field name for different concepts. Some databases, like pubs, have
some fields prefixed with the first two letters of the table, like au_fName,
and given than not all tables follow that convention, even inside authors,
not all of its columns get their au_ , it is more painful than otherwise.



I never used a prefixing convention for SQL syntax, that just have
tendency to make the statement harder to read, imho, and without real
benefit associated to the extra effort.

If you handle SQL related object through VBA code, that is a matter of
VBA, not SQL, on the other hand. But really, do we need a naming convention
that is not respected "from time to time"? It soon becomes unreliable
(changing the data type of a variable without renaming its prefix
accordingly), and finally bring more noise than anything else. VBA, C++ and
SQL are not really susceptible to problems encountered in C, where it is
probably the only place where a following a naming convention makes sense. I
don't say to not follow any prefix-type naming convention, even in VBA, I
personally use m_ for class members, but just if you need some moral
support in NOT using any extensive one, you have mine. You also have dot-Net
recommendations (to not use any prefixing type naming convention) on your
side.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top