Easy nomenclature question

[GrandmasterB]

Hi all. So far this forum (three days) has been helpful, and I've searched through past posts to see if this question was asked, but haven't found anything, so here's another thread:

I have tables

tblCustomers
tblVendors

(actually there are about 15 tables, but these two will be good reps of the issue).

In each table are fields like

Name
Address1
Address2
City
State
Zip
.
.
.

So, there are similar fields across multiple tables.

What is the standard nomenclature here? I've basically structured it as

tblCustomers
CustomerName
CustomerAddress1
.
.
.

and

tblVendors
VendorName
VendorAddress
.
.
.


Am I just wasting effort and making it more difficult to type out when writing queries or code, etc, or is this standard? I've noticed this makes it really easy to know what I'm actually referencing, but I find myself having to type out the same thing over and over.

I know this is like DB 101, but as I mentioned before, I'm self taught and most of you seem kinda smart.

What do you think?

 

[The_Doc_Man]

There is no standard for such things in the industry. However, if you work for a large corporation, you might have a programmer's style guide that addresses the topic.

To me, the pragmatist's rule is the name has to be long enough to have mnemonic value, unique enough to avoid stepping on similar names and system keywords, and short enough to reduce the odds of getting carpal tunnel syndrome when working on your DB. Somewhere among those guidelines is the happy medium.

 

[GrandmasterB]

Maybe like shortening it to

CustName
and
VendName

..?

I'm thinking about this along the lines of somebody someday having to work on this DB that I'm rebuilding, and them going, "God, this guy was a moron!" ...on top of that, I just want to make sure I follow industry standards, whatever those may be.

The rest of the story is that I'm at a small engineering company, and everything was done manually, or for this project tracking, entered directly into tables which served like glorified spreadsheets. So, with my limited knowledge, I'm trying to help them make the leap into the 90's.

 

[dkinley]

I'm thinking about this along the lines of somebody someday having to work on this DB that I'm rebuilding, and them going, "God, this guy was a moron!" ...

Hehehe. It doesn't matter what you call fields or variables, someone is going to come along with their own way and say that just as you will of someone else (unless as Doc says there is an in-house programmers guide).

The only industry standards are things like camel case or underscores, different typs of naming conventions, usage of reserved words, etc., but these are usually defined in the guide.

The only way to not be called a moron is to document, document, and document! For instance, use the descriptions column in table design. I go one step further and keep all of this in a seperate document. This will come in handy when you are working in other areas of the db or pass this along to someone else as a reference.

The same for functions. I document those in-line with code and carry it on the applications supporting documents. I also have logic diagrams of complex functions so when I go back a year later to revisit something I don't have to relearn my functions in order to modify or fix them.

Then they can call you a moron for incorrect logic but say you are ultra-smart for the documentation and give you the respect of a programmer.

-dK

 

[Pat Hartman]

I like to use the same name for the "same" field in different tables so I would use "City" in both the customer and vendor tables but more likely, I would create an address table and rather than maintaining address information in separate tables, lump it all together. The advantage of lumping is that you end up with subforms that can be placed on multiple main forms and save yourself some work especially if there is editing required.

You need to be aware of reserved words and avoid them in your column names. Things like Name and Date will come back to haunt you once you get to VBA. In those cases, I use prefixes or sufixes whichever seems more natural. So I would use CustName, CustID, FirstName, ReceivedDate, VendID, etc.

Some people use data type as a prefix - I would never do that at the table level although I do do it for variable names in code. Some people also prefix all columns of a table with a common set of characters - I also would never do that although when I worked in COBOL, I did but the environment was different. When you create queries, the column names are prefixed by the table name so you end up with Customers.CustName, Customers.CustAddr1, etc. which is simply redundant. In code when working with recordsets, the recordset has a name so the same thing applies - rsCust!CustName, rsCust!CustAddr1, etc.

 

[Mike375]

I name queries, forms etc with names that will group them. Although I think I read somewhere that A2007 sorts differently. Personally, I find naming like tblSomething or fmrSomething very hard to read.

I make field names very short. Eg, Often use LN for LastName field etc. For fields that are similar such as those for the various parts of an address I often use A1, A2, A3 etc.

 

[The_Doc_Man]

My final bit of advice is simple: If there is no standard at your company, ESTABLISH one. Document that first. Then follow your own rules.

 

[GrandmasterB]

My final bit of advice is simple: If there is no standard at your company, ESTABLISH one. Document that first. Then follow your own rules.

At this point I am erring on the side of excessive info, though I'm starting to reduce the "prefix" I use a little.

Transaction Date and transaction quantity are now TransDate and TransQty. VendorName, et al, is going to stay that way because it is already sprinkled throughout queries, forms, and code.

...oh yeah! Is there a way to change a name and have it populate throughout the entire database, VBA code included? As far as I know, that doesn't exist.

By the way, thanks again. You folks are helpful.

 

[Pat Hartman]

If you turn on the name autocorrect feature, it will propagate name changes to queries, forms, and reports. you're on your own for other changes. There are products on the market and even shareware that will change all names just search here for recommendations.