Arthur Fuller
artful at rogers.com
Tue Dec 28 13:57:55 CST 2004
Well, JC, I beg to differ. In fact I wrote a SQL Tip for builder.com on
this subject, proposing a new convention, and that tip received more
responses that any other tip ever published by builder.com. I have grown
extremely tired of classic Hungarian notation, because I feel that it
increases noise at the expense of signal. I call my new convention
Object-Action. It subscribes to most of your rules but insists that the
"prefix" become a "suffix" -- the reason for this change may have to do
with the fact that by and large I live in the MS-SQL world rather than
the MDB world, but still, I like things to sort in an intelligent way --
without having to read past the prefixes before obtaining useful
information. This is especially true in AXP, where the Queries tab rolls
in views and sprocs within a single tab. I DO NOT want to have to ignore
the first N letters in order to read the information. If every single
value begins with "tbl" or "vue" or "sproc" then why bother? Make that a
suffix instead, so the stuff related to "Customer" sorts alphabetically
in the place where I would expect to find it. Having located the object
of interest, I can then examine the suffix and determine what sort of
object it is.
Again, this may pertain more to MS-SQL than Access. But let's suppose
that there are 3 objects related to the Customer table:
Customer_Select_sproc
Customer_Select_udf
Customer_Select_vue
Customer_tbl
In a normal sort they are presented in exactly this way, and assuming
that the developer was throrough enough to include description
attributes, then I know what I'm dealing with right away. If I need to
do something to the customers table, I know exactly where to look, and
everything sorts accordingly.
Not to say that your convention doesn't work. Merely to say that mine
works better. LOL!
Arthur
John W. Colby wrote:
>I cannot count the number of times I've had to work on databases with no
>naming convention. So I go to find the people table (no tbl of course) and
>there are forms named people, queries named people, combos named people,
>reports named people. My rates go up 50% if I have to deal with that.
>
>John W. Colby
>www.ColbyConsulting.com
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
>Sent: Tuesday, December 28, 2004 9:30 AM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Naming Convention (Query SQL correct but not visible
>indesign view)
>
>
>John,
>
>You've really thought this through! I will no longer give FK's the same
>name as the primary key.
>
>I do one thing for a specific reason. When naming sub forms or reports they
>go like this:
>
>frmPeople or frmPeopleMain, and frmPeopleSub.
>
>I do this so that all the People related forms are adjacent in the database
>window.
>
>Something else I do that is to almost never use an abbreviation in a field,
>variable, control, or label name. This allows me or someone else to pretty
>much be able to read the code to see what's happening without my having to
>put in explanatory comments. It seems that I take more time writing and
>modifying comments than I do writing code, so minimizing comments is good.
>Also, when I can read code, I cannot misinterpret what is happening.
>
>My names could look like:
>
>tblPeopleMain
>tblPeopleSub
>lblDesignEngineer
>txtDesignEngineer - field name = DesignEngineer chkDesignEngineer - field
>name = DesignEngineerApproval dteDesignEngineer - field name =
>DesignEngineerDate stgDesignEngineer varDesignEngineer
>
>. . . . and so on.
>
>You said you use an autoexec macro (I've never used macros in Access). I
>use an invisible startup form, which calls a list of procedures and
>functions in a standard module. Do you know of an advantage one way or the
>other?
>
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
>Sent: Monday, December 27, 2004 8:07 PM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Query SQL correct but not visible in design view
>
>Jim,
>
>I actually have a very rigorous naming convention that I do follow,
>religiously. My convention is:
>
>I NEVER use special characters in any object name -
>`~!@#$%^&*()_-+=|\}]{["':;?/>.<,|\ are all forbidden characters in an object
>name (as well as a space).
>
>I ALWAYS prefix objects - frm/sfrm, rpt/srpt, qry (or qxx), tbl/tlkp/tmm, -
>and of course I don't use macros except for autoexec and autokeys, neither
>of which can have a mac prefix.
>
>I do NOT prefix fields with fld
>
>I do NOT put the data type in the field name. I have just had too many
>instances where the data type changes, and it is a PITA doing the search and
>replace to make the change.
>
>In field names, I use the first two characters of the table name, unless the
>name is multiple words, in which case I use the first letter of each word.
>
>tblPeople = PE
>tblAutos = AU
>tblPeopleAutos = PA
>Etc.
>
>I then use an underscore to delineate the end of the table name prefix
>
>PE_
>PA_
>
>I then use ID for the PK
>
>PE_ID
>PA_ID
>
>I use words, sometimes abbreviations for the words
>
>PE_LastName or
>PE_LName
>
>Foreign keys are the PK name in the "parent" table, with the underscore
>removed, and the 'ID' coming first
>
>tblPeople
>PE_ID <<<
>PE_Lname
>
>tblAutos
>AU_ID <<<<
>AU_Model
>AU_Color
>
>tblPeopleAutos
>PA_ID
>PA_IDPE <<<<
>PA_IDAU <<<<
>
>I place all FKs at the top of the table so that they can be easily found in
>any table. I "swap" the ID and the table prefix so that it is obvious that
>a field is a FK. If there is an ID after the underscore, then the field is
>a FK (or the PK of the table). _IDPE, _IDAU etc.
>
>This convention makes it dead simple to work with tables. There is NEVER a
>field name in two different tables with the same field name, since the table
>name prefix is always unique. I never get the "can't figure out what table
>this field comes from" message from the query builder.
>
>It is dead simple to look at a FK and "see" what the PK is called in the
>"parent" table:
>
>PA_IDPE <<
>
>Take the IDPE, swap the ID and PE, and place an underscore between them
>
>PE_ID
>
>After using this convention for a little while, it "just works" and makes
>the process of finding relationships between tables a breeze.
>
>I have run into a few instances where there are hundreds of tables where I
>have to maintain a table of prefixes to ensure that there aren't collisions
>(two tables with the same prefix). In those instances it I usually have to
>go with more than two characters to denote a table name word.
>
>I have used this convention since about 1995 or thereabouts. I have shown
>many developers how to use it and most of them love it.
>
>John W. Colby
>www.ColbyConsulting.com
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
>Sent: Monday, December 27, 2004 11:41 AM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Query SQL correct but not visible in design view
>
>
>Do you use foreign key field names that are different from the primary key
>name in the parent tables? What sort of naming convention do you use? I
>always find it easier to understand a database when I can track fields such
>as fldFirstName throughout a table structure. However, I must confess I
>don't religiously follow this naming convention myself. Jim Hale
>
>-----Original Message-----
>From: John W. Colby [mailto:jwcolby at colbyconsulting.com]
>Sent: Saturday, December 25, 2004 12:40 PM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Query SQL correct but not visible in design view
>
>
>That would explain it. I do not use the table qualifier unless the field
>name is the same in different tables which is generally not the case in my
>systems.
>
>John W. Colby
>www.ColbyConsulting.com
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
>Sent: Saturday, December 25, 2004 12:23 PM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Query SQL correct but not visible in design view
>
>
>John,
>
> If an SQL statement is put directly in SQL view of QBE, without having
>first dragged any field into the QBE grid, relevant fields get displayed in
>the grid only if their names in SELECT clause are qualified by the table
>name.
>
> For example -
> (a) SELECT * FROM tbl1 - will not show any field in QBE grid.
> (b) SELECT tbl1.* FROM tbl1 - will result in correct display in QBE
>grid.
>
> Note - This is as per tests conducted on Access XP running on Win XP.
>
>A.D.Tejpal
>--------------
>
> ----- Original Message -----
> From: John W. Colby
> To: 'Access Developers discussion and problem solving'
> Sent: Friday, December 24, 2004 09:04
> Subject: [AccessD] Query SQL correct but not visible in design view
>
>
> I am building a SQL statement "on the fly" which essentially compares
>matching fields in two different tables - Tbl1.Lname <> Tbl2.Lname. The SQL
>runs but only the tables and their joins are visible in the QBE grid. There
>are no fields visible etc. Does anyone understand what causes Access to do
> this?
> I have had the same thing occur even with something as simple as a SELECT
>* from tbl1 inserted into the SQL view. Tbl1 is displayed, but nothing in
>the grid. The query executes correctly.
>
> Anyone?
>
> John W. Colby
> www.ColbyConsulting.com
>
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.298 / Virus Database: 265.6.5 - Release Date: 12/26/2004