Field naming conventions

[Tekime]

First of all, I apologize if this is the wrong place to post this question; but I'm using Access and it doesn't seem to fit in the other forums, so... here goes.

Does anybody feel it is smart to prepend field names with a unique identifier to the table it belongs to?

For example, I have a table tbl_Transactions with fields for transaction date, amount, authorization code, and status. Rather than storing "chargeDate", "amount", "authorizationNumber" and "status", I almost feel more comfortable naming them "trns_Date", "trns_Amount", "trns_AuthorizationNumber" and "trns_Status".

Does anybody else follow this sort of convention, i.e. prepending all of their field names with an abbreviation of the table name it belongs to? I don't see this in practice a lot, but I'm starting to use highly complex queries that could potentially pull like-named fields.

I guess I would just like to see what everyone else is doing, and what the Right thing to do is when it comes to developing forward-thinking applications.
Thanks

http://www.fury-tech.com

 

[pduncan]

I do the same - it helps me double check my SQL statements. It does add a little extra typing but when I see a particular data element, I know exactly where it came from

 

[Tekime]

Exactly, I like knowing where it came from.

Now how do you handle primary keys? I modified my tables and prepended a shorter version of the table name. Ex. my transactions table now looks like:

tbl_Transactions
- transactionId
- trns_orderId
- trns_date
- trns_amount
- trns_authorizationNumber
- trns_status

Would you have named the primary key trns_transactionId, trns_id or just left it as is? Or would it be the best to name everything with the full table name, as in transaction_id, transaction_orderId, etc...

Also, I have a many-to-many table which associates transactions with products. The primary key is the combination of the two foreign keys, as such:

tbl_Transactions_Products
- transactionId
- productId
- trPr_quantity

In this case would you name the primary key(s) trPr_transactionId and trPr_productId?

Thanks for any input, I know this is probably more of an opinion issue but I am really interested in how you folks manage your field names.

Thanks

http://www.fury-tech.com

 

[JeremyNYC]

I don't do this. For one thing, when I have a foreign key, I want it to have exactly the same name as it does in the table in which it's the primary key. I certainly understand the motivation to do it, but it alwasy seemed to make my field names too long for me to like them.

There are formal naming conventions available on the web. The one I learned from (not that I follow it religiously, and it may even include the practice you're asking about) is the Reddick VBA Naming Convention, which, if I remember correctly, is based on Hungarian. I definitely use my own, undocumented, version of it.

You can get that one at

http://www.xoc.net/standards/rvbanc.asp.

Whatever standard you follow, you should follow some standard--even if it's one you make up yourself. Consistency is important.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[dhookom]

I also use the first three characters of my field to identify the table. It really helps in queries where you don't have to use the table!field in expressions. Typical:
tblEmployees
empEmpID autonum pk
empLastName
empFirstName

tblProjects
prjPrjID autonum pk
prjTitle

tblProjEmps
prePrEID autonum pk
prePrjID
preEmpID

All primary-foreign key relationships have the same last 5 characters.