Naming tables and columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wish some one can suggest me the best way to name tables and columns.
For example:
Should I use underlines?
Should I use the table name inside the column name? and if so, should I
have the table name before the column name?
To have the Autonumeric Id of each table named just Id, could be confusing?

Thanks a lot,
Lina
 
It's kinda up to you what you find to be the most intuitive and
maintainable.
I try to name all my tables and fields like:

tblEmployees
empEmpID autonumber primary key
empFirstName
empLastName
emp...

tblProjects
prjPrjID autonumber primary key
prjTitle
prjStaID

tblProjectEmployees
prePrEID autonumber primary key
preEmpID link tblEmployees.empEmpID
prePrjID link tblProjects.prjPrjID

Every field name in an MDB is unique and begins with three letters that
identify the table. The autonumber primary keys repeat the first three
letters and always have the 7-8 characters of ID.

Any field ending with "ID" is long integer and either a primary or foreign
key. I prefer to not use underscores.

Tony Toews has posted his standards at
http://www.granite.ab.ca/access/tablefieldnaming.htm.

Bottom line, evaluate your options and choose one.
 
Big question!

It's largely a matter of style, provided you don't use reserved words or
others that are known to be ambiguous. Unfortunately, that's a large list.
There's over 1200 listed here:
http://allenbrowne.com/AppIssueBadWord.html
or you can get Access to check your database with this utility:
http://allenbrowne.com/AppIssueChecker.html

There are suggested naming conventions such as:
http://www.xoc.net/standards/rvbanc.asp#DAO

Answers for your specific questions in-line below.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lina Manjarres said:
I wish some one can suggest me the best way to name tables and columns.
For example:
Should I use underlines?
Click to expand...

Underscores are fine.

I don't use them personally, as it's just one more unnecessary character.
Instead, I use mixed case such as CompanyName or OrderID.
Access remembers the case, but is not case-sensitive.
Should I use the table name inside the column name? and if so, should I
have the table name before the column name?
Click to expand...

Some developers use the table name as part of the field name, and it works.

Again, I don't because it makes lots of extra typing.
Where I need to distinguish the same names from different tables, I can:
- use the TableName.FieldName syntax, or
- alias the field.

Leaving the names the same has some advantages. For example, if you have an
OrderID primary key in tblOrder, and an OrderID foreign key in
tblOrderDetail, Access guess the LinkMasterFields/LinkChildFields correctly
when you create a subform.
To have the Autonumeric Id of each table named just Id, could be
confusing?
Click to expand...

You could get away with it, but I think it makes sense for the primary key
to include the table name.

Again, it makes the joins more obvious: tblOrder.OrderID probably matches
tblOrderDetail.OrderID.
 
Allen said:
Big question!
Click to expand...

Indeed. This area warrants a whole book. Happily there is such a book.

Joe Celko defends his own convention in his SQL Programming Style book,
both of which I use and recommend.

It is primarily based on readability e.g. keywords in upper case (e.g.
SELECT), table names in Pascal case (e.g. BudgetPeriod) and column
names in lowercase with each word separated by an underscore (e.g.
last_name).

Formatting aside, it is based on ISO 11179-5 naming conventions, The
ISO standard is more flexible because it uses Pascal case *and*
underscores e.g. contrast:

Cost_BudgetPeriod_TotalAmount (ISO)
cost_budget_period_total_amount (Celko)

The ISO convention makes it easier to pick out the class name,
qualifier term, etc, which is great for a data dictionary. For SQL
code, contrast:

SELECT C1.budget_period_total_amount FROM CostAnalysis AS C1 (ISO)
SELECT C1.BudgetPeriod_TotalAmount FROM CostAnalysis AS C1 (Celko)

The Celko convention makes it easier to spot the table name among the
columns

One point that is often overlooked is that the naming convention should
be consistently applied throughout the data model. This does not
necessarily mean that the name does not change e.g. the column may be
named 'last_name' in the Customers (pluralized because it is a
collection of customers) table but would be named customer_last_name in
the OrderDetails resultset.

To help with consistency, a data dictionary is almost essential i.e.
one that aids the design of the schema (rather than a 'picture' of the
schema after the event).

I recall someone suggesting the data dictionary hold a three character
correlation name (alias) for each table in the schema, with the aim of
greater consistency. A bit strict but does strike me as a good idea
worth repeating.

As for whether table prefixes based on physical characteristic or
Access UI tab (tbl, qry, vw, etc) are recommended, see the recent
discussion:

http://groups.google.com/group/micr..._frm/thread/c1c5bf9360c36749/c4415d1ac8f81278

The thread also touches on the motivation for having a convention. My
motivation is to make my SQL code easy to maintain by both me, my
colleagues and the poor sod who inherits it when I'm gone.

Jamie.

--
 
I think you've reversed the labels in the second example:

SELECT C1.budget_period_total_amount FROM CostAnalysis AS C1 (ISO)
SELECT C1.BudgetPeriod_TotalAmount FROM CostAnalysis AS C1 (Celko)

Based on your descriptions, the first one uses Celko's convention, while the
second one uses the ISO convention.
 
Douglas said:
I think you've reversed the labels in the second example:

SELECT C1.budget_period_total_amount FROM CostAnalysis AS C1 (ISO)
SELECT C1.BudgetPeriod_TotalAmount FROM CostAnalysis AS C1 (Celko)

Based on your descriptions, the first one uses Celko's convention, while the
second one uses the ISO convention.
Click to expand...

Good catch! Thanks.

Jamie.

--
 
Back
Top