Naming tables and columns

[Lina Manjarres]

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

 

[Duane Hookom]

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.

 

[Allen Browne]

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.

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

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?

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?

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.

 

[Douglas J. Steele]

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.