John W. Colby
jwcolby at colbyconsulting.com
Tue Dec 28 14:25:35 CST 2004
Arthur,
That of course is a simple matter of preference, not a matter of one is
inherently better than the other. In fact I don't much care which is used
as long as something is used. I do prefer prefixes because the eye is much
faster at finding the beginning of a name than the end of a name. Thus if I
have a SQL statement with 5000 characters, I can scan (read) the statement
and the objects pop out of every object name. tblPeople is much easier to
determine that it is a table than PeopleTbl, and even more so when there are
3 or 4 words. If you are scanning something looking for a reference to a
form or a control, it is just plain easier to find those objects when the
"xxx" is a prefix.
In the end though, for purposes of doing a search using a search tool, it
really truly DOESN'T matter. tblPeople or PeopleTbl are equally
distinguishable and easily found. If anything can be named People (forms,
reports, queries, combos, text boxes etc. then we are talking chaos and an
automated search (without an eyeball guiding it) becomes literally
impossible.
Again, it is a matter of opinion whether to use prefixes or suffixes. I use
prefixes, I have tried suffixes and always went back to prefixes, but in the
end either distinguish the object by type and that is the most important
thing.
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 Arthur Fuller
Sent: Tuesday, December 28, 2004 2:58 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Naming Convention (Query SQL correct but not
visibleindesign view)
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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com