Appendix A: The Rules
© 2007 The Smart Method Ltd
281
2: Table and Field naming
2-1
Never use prefixes for table names.
Access developers commonly prefix table names with tbl. We don’t like this convention. The
primary object in an Access database is the table so we prefer the lack of any prefix to identify this
type of object.
Table name prefixes are also incompatible with the “Field names are always prefixed by the table
name" rule.
2-2
Primary keys are named using the syntax: <table name> + <ID>
Example: Table named Customer
Primary Key: CustomerID
2-3
Foreign keys always have exactly the same name as the related primary key.
Example: The primary key MediaID in the Media table is also called MediaID when used as a foreign
key in the Film table.
2-4
If not obvious, the unit of measure is incorporated into the field name.
Example: A field is needed to indicate the length of a film. It is not clear whether the data will be
expressed in hours or minutes.
Good: FilmLengthMinutes
Bad: FilmLength
2-5
The link table in a many-to-many relationship is always named with the names of the tables on
either side of the many-to-many relationship.
Example: Two tables have a many-to-many relationship, the Film and Actor tables.
A link table named FilmActor is created to model the relationship.
2-6
Apart from Foreign Keys, field names are always prefixed by the table name.
This is one of those golden rules that (in conjunction with the Cradle to the Grave Naming Convention)
will massively increase your productivity and the reliability of your code.
Because all table names in a database are unique, every field name in your database (apart from
Primary and Foreign Keys) will also be unique if you prefix all field names with table names. This
provides huge benefits:
Many reporting tools (including Access) have query designers that automatically create joins for
fields of the same name. Because we have a rule that foreign and primary keys always have the
same name, the query designer will get it right every single time. There will never be any
spurious joins caused by occurrences of the same non-key field name in different tables.
SQL queries become a lot simpler when this rule is in place as you will not have to explicitly
qualify every field name with a reference to the related table.
Code becomes more readable and less error-prone:
strCustomerFirstName = “James"
'
I know this originated in the Customer Table
strEmployeeFirstName = “Peter"
'
I know this originated in the Employee Table
strFirstName = “Paul"
'
Which table did this come from. I’ll have to guess or
'
trace the code.
