Lookup table naming convention

[Christopher Glaeser]

For design simplicity, all of my lookup tables have two fields, an autonum
for the primary key and text field for the value. Example:

tblPriority
PriorityID: Autonum
Priority: Text

where the priority is "ASAP", "Normal", and "Low".

Assume tblWorkOrder includes a link to tblPriority. What do you name this
field? Priority or PriorityID?

Best,
Christopher

 

[Duane Hookom]

I always name fields with the first three characters unique to each table.
For instance tblWorkOrder would have fields like:
worWOrID
worOrigDate
worTitle
worStatus
worPriID 'link to tblPriority.priPriID

This suggests that no two fields in an application have the same name. All
primary keys consist of the first three characters repeated followed by
"ID". This system has worked very well for me.

 

[Armen Stein]

I always name fields with the first three characters unique to each table.
For instance tblWorkOrder would have fields like:
worWOrID
worOrigDate
worTitle
worStatus
worPriID 'link to tblPriority.priPriID

This suggests that no two fields in an application have the same name. All
primary keys consist of the first three characters repeated followed by
"ID". This system has worked very well for me.

Hi Christopher,

Here's another standard that has worked well for us, for databases from
10 to 150 tables.

Name every field in a table with table name included, like this:

WorkOrderID
WorkOrderOrigDate
WorkOrderTitle

Sometimes we consistently shorten the table name if it's very long, but
we've found that "WorkOrder" is more easy than "wor" to recognize years
later. The field names seem long at first, but you get used to them,
and they are very easy to understand.

Foreign keys can be named exactly as in their Primary table. This sets
them apart visually from the "local" fields, and there's no question as
to where they refer:

PriorityID

If you have more than one foreign key reference to the same table's
primary key, or if you need some clarification, use a suffix:

CustomerID_Billing
CustomerID_Shipping

EmployeeID_ReportsTo

PriorityID_WorkOrderDefault

Just another opinion,

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com