Pointless debates on the finer points of naming your objects (moved from Combo Box Requery thread)
Robert Morley
I've seen countless numbers of experienced, formally trained (and who
learned it on their own) database programmers on every platform I've ever
used who use "tbl" for tables, "vw" for views, "usp" for user stored
procedures, not to mention "frm" for forms, "rpt" for reports, etc., ad
nauseum.
Personally, I don't see the point of starting ALL objects in a category with
the same letters; that's generally redundant (though occasionally useful
when you're trying to distinguish between views and tables, etc.). My
general preference is to base it around the function of the object. "acct"
for account-related tables, "resp" for respondent-related tables, "list" for
simple lookup-type tables, etc. Only for objects that don't have a
significant inter-relationship with the rest of the database (i.e.,
localization tables, user preferences, etc.) do I use the generic "tbl",
"frm", or whatever.
And while "tbl" itself may have first appeared in a Smart Access article in
1993, as I said, Hungarian notation itself pre-dates that. As I said,
Simonyi Károly (aka Charles Simonyi) did indeed work at Microsoft, but he
started Hungarian Notation back when he was working for Xerox. It's hardly
a surprise that it later appeared in a Microsoft product that he worked on.
Hell, ignore Hungarian notation for a moment, how long ago did people start
using "i" for integer? "For i = " was one of the first constructs I learned
almost 30 years ago.
Rob
"Craig Alexander Morrison" <c...@microsoft.newsgroups.public.com> wrote in
message news:ulTN4sYp...@TK2MSFTNGP03.phx.gbl...
>> Oh and just for the record, Hungarian Notation became popular with
>> languages like VB/VBA, but actually pre-dates it. The inventor, Simonyi
>> Károly, was working for Xerox at the time and only much later did he move
>> to Microsoft.
>
> For the record "tbl" and other such fripperies first appeared in a 1993
> Smart Access article and has subsequently appeared in the ADH books. I
> don't
> mind one using tags in code but not for database objects. Charles Simonyi
> actually worked on Access 1; I am not sure what he thinks of the
> Lesynski/Reddick extensions.
>
> It is a good sign of an amateur with limited experience of other products.
> Some amateurs are very good programmers though.
>
> Nearly all formally trained Relational (or SQL) Database designers would
> find this "tbl" tag laughable.
>
> --
> Slainte
>
> Craig Alexander Morrison
> Crawbridge Data (Scotland) Limited
>
> "Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message
> news:%237AzQEW...@TK2MSFTNGP03.phx.gbl...
>> Like I said, different ways of thinking. I look at most of your points
>> and disagree with them either in part or in whole, but frankly, this
>> isn't
>> the place to get into this kind of discussion. The original post has
>> been
>> answered with two different solutions, and that's the end of it as far as
>> I'm concerned. I just bitched someone else out in another NG for exactly
>> this kind of "mine is bigger than yours" discussion that serves no
>> purpose
>> but to bicker pointlessly. Everybody's got their favourite apps and the
>> apps they think are toys, we simply disagree on which ones are which.
>>
>> :)
>>
>>
>>
>> Rob
>>
>> "Tim Ferguson" <Fergu...@softhome.net> wrote in message
>> news:Xns97FDE698BF5F...@207.46.248.16...
>>> "Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in
>>> news:uu9k#kPpGH...@TK2MSFTNGP05.phx.gbl:
>>>
>>>> Obviously someone thought so, or they never would've designed combo
>>>> box lookups to work in tables.
>>>
>>> There are several things in Access that relegate it into the "toy"
>>> platform in the eyes of other database developers on "real" systems. The
>>> quaint but misguided fashion for putting "tbl" in front of object names
>>> is one; the presence of the "look up field" is another. I regret this
>>> because when you get up close, Jet is a pretty fine database engine and
>>> Access is a flexible and usable rapid development platform, but it will
>>> continue to get a rotten press as long as it's aimed at the Janet and
>>> John level of user. The type of users, in effect, that get drowned in
>>> any
>>> case as soon as they step off the dumb-spreadsheet kind of appliction.
>>>
>>> FWIW, it seems that the Access-as-toy party has won the debate because
>>> Jet development is being taken over by the Access UI team. I think it's
>>> time to be off to MySQL before they put in the paper clip telling you
>>> not
>>> to put financial data into an integer field.
>>>
>>>> What it really comes down to is that each of us has our own opinions
>>>> and ways of doing things. Don't get upset with someone just because
>>>> they think and work differently than you do.
>>>
>>> I get upset because of two things. Firstly, posts like yours may be seen
>>> by people who know about databases but not much about Access, who will
>>> merely have their suspicions confirmed that access is a plaything for
>>> people who don't know their way round Codd or Date. The second reason is
>>> that they may be seen by people who don't know much about Access or
>>> databases, and who will then think this is a good and reasonable way to
>>> use it; and whose horizons will forever be shortened.
>>>
>>> All the best
>>>
>>>
>>> Tim F
>>>
>>
>>
>
>
BruceM
you choose. For myself it would be confusing if account-related tables,
queries, forms, and reports all start with the same prefix, but that's just
my preference.
"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message
news:uhstMLcp...@TK2MSFTNGP05.phx.gbl...
Craig Alexander Morrison
See original thread. (vbg)
--
Slainte
Craig Alexander Morrison
Crawbridge Data (Scotland) Limited
"BruceM" <bam...@yawwhodawtcalm.not> wrote in message
news:uid3uTcp...@TK2MSFTNGP03.phx.gbl...
Robert Morley
amateur even more than I hate being drawn into pointless debates. :)
Rob
"BruceM" <bam...@yawwhodawtcalm.not> wrote in message
news:uid3uTcp...@TK2MSFTNGP03.phx.gbl...
Sylvain Lafontaine fill the blanks, no spam please
some piece of code, you must write it in a way that will convey the maximum
quantity of useful information to the programmer but without cluttering the
whole thing, because at this point the process will become
counter-productive: instead of diminishing the possibility for the
programmer of writing a bug, it will increase it.
« how long ago did people start using "i" for integer? "For i = " was one
of the first constructs I learned almost 30 years ago. »
For those interested, this old notation came from the first commercial
version of Fortran and had then a functional purpose: all variables
beginning with one of the letters i, j, k, l, m and n (taken from the
enumeration i .. n corresponding to the first two letters of the word
INteger) were automatically declared to be of type integer and all others
were dimensionned as float by default.
In fact, in Fortran 4, I'm not even sure if you could dimension a variable
beginning with one of the letters i .. n to *not* be an integer. (Since my
old manual of Fortran 4 is gone since a very long time, I can't no longer
verify this point.) In Fortran 5, you can easily declare one of these
variables to not be an integer but still, if you don't say otherwise, they
will be of type integer by default.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message
news:uhstMLcp...@TK2MSFTNGP05.phx.gbl...
Robert Morley
programming even longer than I have! ;)
Rob
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uqvv8fcp...@TK2MSFTNGP05.phx.gbl...
Robert Morley
> writing some piece of code, you must write it in a way that will convey
> the maximum quantity of useful information to the programmer but without
> cluttering the whole thing, because at this point the process will become
> counter-productive: instead of diminishing the possibility for the
> programmer of writing a bug, it will increase it.
Actually, I found a hilarious web page about that by yet another Canadian:
http://mindprod.com/jgloss/unmainnaming.html
BruceM
convention would sort of cheapen the program in the view of some. I'm not
clear if those who think poorly of using prefixes for objects would prefer
no naming convention at all, or what exactly, but in any case it seems in
rather a different category than lookup fields in tables.
When I first started learning about Access I was taught to put prefixes onto
fields (txt for a Text field, dat for Date/Time, and like that), but I soon
discovered that I much prefer to use prefixes for controls in particular. I
also use them prefixes objects, so I therefore know that if it does not have
a prefix it is a field. Works for me, even if it offends some.
Best regards.
"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message
news:%231PvFYc...@TK2MSFTNGP04.phx.gbl...
Tim Ferguson
> And while "tbl" itself may have first appeared in a Smart Access
> article in 1993, as I said, Hungarian notation itself pre-dates that.
> As I said, Simonyi K roly (aka Charles Simonyi) did indeed work at
> Microsoft, but he started Hungarian Notation back when he was working
> for Xerox.
For a good description of the history of "systems Hungarian" and its
misapplication see Joel on Software:
http://www.joelonsoftware.com/articles/Wrong.html
Tim F
mnature
> If the debates are pointless, why are you adding to them?
Arguing with programmers is like wrestling with a pig in the mud.
After a few hours, you realize that the pig likes it.
Robert Morley
Systems & Apps Hungarian (though I can never remember their formal titles
until I re-read articles such as that one...the names just never made sense
to me).
Going back to the "tbl" concept, though, at some point in this long chain of
messages over multiple threads, I seem to remember stating that I only use
"tbl" for generic tables that have no other logical grouping within my
database (or code, or whatever it is I'm looking at), and that I tend to
group and name tables by logical function ("acct", "resp", etc.)
otherwise...that it seemed a little redundant to name everything of a
certain object type or data type with the same prefix. Isn't that basically
what the article is advocating?
I *do* consider Systems Hungarian to be a perfectly valid alternative for
those that find it's useful to them, though for what I do, I find it a
little limited in its own right. My personal preference is to use a hybrid
of systems & apps when coding, where the first lower-case prefix is
descriptive of the data type (with reasonable exceptions...I don't know
ANYBODY who uses something like lngHWnd), and the first upper-case prefix is
descriptive of the logical grouping, (i.e. strAcctFilename, which is very
obviously a string relating to an account, and is a file name). But there
are those who prefer Systems Hungarian, and as long as they keep it
localized to their own code/database/whatever and don't try to impose it on
mine, that's fine. (As you can imagine, I was NOT best pleased with the Web
developer who went and copied all my stored procedures that started with
"web" to indicate they were used on the Web to a simple "usp" prefix. Who
on earth ever gave System Administrator privileges to a *student* web
developer?!?)
Anyway, all things considered, I think we mostly just misunderstood each
other from the start of this conversation. You know what the say about
assumptions! :)
Rob
"Tim Ferguson" <Fergu...@softhome.net> wrote in message
news:Xns97FED3B6FFD2...@207.46.248.16...
Robert Morley
> After a few hours, you realize that the pig likes it.
Dare I ask how you came to this conclusion (about the pigs, that is, not the
programmers)? <grin>
Rob
mnature
After arguing with programmers, the pig was a nice break . . .
Robert Morley
Rob
"mnature" <mna...@discussions.microsoft.com> wrote in message
news:B07EAAFE-23F2-456A...@microsoft.com...
Jamie Collins
Robert Morley wrote:
> I've seen countless numbers of experienced, formally trained (and who
> learned it on their own) database programmers on every platform I've ever
> used who use "tbl" for tables, "vw" for views, "usp" for user stored
> procedures, not to mention "frm" for forms, "rpt" for reports, etc., ad
> nauseum.
I think the important word here is 'convention'.
It is an Access convention to prefix tables with 'tbl'. Wannabe Access
MVPs see established Access MVPs using the prefix so they imitate them;
in turn Access MVPs use the prefix because it's what their audience
expects i.e. to do Access things in an Access way.
Bottom line: use the 'tlb' prefix if you want to appear to be a true
blue Access user. How that affects your reputation as an amateur or
otherwise will largely be determined by where you are posting your
reply e.g. contrast the Microsoft.Public.Access.GettingStarted group
with comp.databases.theory.
My advice: if you want to appear as a 'serious' SQL database type
person, take a look at what people do outside of the Access ghetto.
You'll find the debate focuses on whether to pluralize table names
(e.g. Customer or Customers) and that prefixes are not rarely used at
all, other than as a hangover from a port from Access.
Jamie.
--
Jamie Collins
Robert Morley wrote:
> "tbl" for tables, "vw" for views
Another thought. Could using prefixes encourage the wrong mental model?
For example, using 'tbl' and 'vw' differentiates between a table and a
view (or 'qry for Query, to use the Access conventions). The difference
is physical whereas logically a view is a (virtual) table so why
differentiate at all? If I say SELECT last_name FROM Customers, why
would I care whether the table was virtual or otherwise? What value
does the prefix add?
Likewise the terms 'field' and 'record' which still prevail in the
Access world, rather than the respective terms 'column' and 'row'
preferred in the wider SQL world. Do these terms really encourage
people to think in terms file systems and sequential processing rather
than SQL databases and a set-based mental model?
Jamie.
--
Sylvain Lafontaine fill the blanks, no spam please
only because they are writing only a few lines of code instead of a full
database and they need to make these very few lines to be a much clear as
possible; with no other background.
Myself, I often write here things MyTable or MyView; however, I will never
use the names MyTable or MyView in one of MyDatabase.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1152784361.1...@35g2000cwc.googlegroups.com...
Robert Morley
whatever unless there's no other logical prefix, but I can think of at least
one argument in favour of doing it that way: when you're looking at someone
else's code, you know EXACTLY where to go if you need to look at the design
of the table, view, SP, or whatever else.
The record/field vs. row/column discussion is something I've heard before,
and I've always thought that calling them rows & columns to look more
professional or well-educated was "bass-ackwards". To me, spreadsheets have
rows & columns; to apply those terms to a table is to relegate it to the
level of a spreadsheet (or at best, a pre-relational-database table).
But hey, I'll be the first to admit that I started out in Access and
expanded my expertise from there, so maybe my views are a little biased
towards the historical Access ways of doing things. :)
Rob
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1152785248.5...@m73g2000cwd.googlegroups.com...
BruceM
another will appear in the eyes of somebody else. I learned about a naming
convention that makes sense, so I started using it, and found it to be
helpful. It had nothing to do with being a "wannabe". Did you choose the
naming convention you use because you "wannabe" like somebody else? If not,
why do you assume that somebody using another naming convention is driven by
such a motivation?
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1152784361.1...@35g2000cwc.googlegroups.com...
Tim Ferguson
> Like I said, for myself, I don't generally stick to simply "tbl" or
> "vw" or whatever unless there's no other logical prefix, but I can
> think of at least one argument in favour of doing it that way: when
> you're looking at someone else's code, you know EXACTLY where to go if
> you need to look at the design of the table, view, SP, or whatever
> else.
Unless what used to be a table is now a view, (or vice versa, though less
likely). Access does not easily provide the tools to go hunting through
every reference to "tblSomething" and change it to "vwSomething". Nor,
for that matter, "txtDescriptionType" to "cboDescriptionType", but that
is a different argument.
> But hey, I'll be the first to admit that I started out in Access and
> expanded my expertise from there, so maybe my views are a little
> biased towards the historical Access ways of doing things. :)
To be fair, it was Microsoft itself that pushed Hungarian notation as its
own house style. I don't believe that the company ever understood how
good Access was and they don't seem to have cared either. As for the
programming style, they have now reverted completely, and prefixes are
AbsolutelyOut for anything based on the new versions of VisualStudio --
or should that be visualStudio?
Robert Morley
> likely). Access does not easily provide the tools to go hunting through
> every reference to "tblSomething" and change it to "vwSomething". Nor,
> for that matter, "txtDescriptionType" to "cboDescriptionType", but that
> is a different argument.
Yes, I've run into that problem. I currently view called "tblPreferences",
which is a hanger-on from when tblPreferences was stored as a local table in
a replicated MDB, but then got upsized to SQL Server and had to be done as a
view based on the current user.
There are a number of good search & replace utilities for Access...for some
reason, I just haven't gotten around to using it on tblPreferences yet! :)
> To be fair, it was Microsoft itself that pushed Hungarian notation as its
> own house style. I don't believe that the company ever understood how
> good Access was and they don't seem to have cared either. As for the
> programming style, they have now reverted completely, and prefixes are
> AbsolutelyOut for anything based on the new versions of VisualStudio --
> or should that be visualStudio?
Yes, so I've heard. Personally, I don't give a rat's ass what's in or
out...as many have said, I use what works best for me (and since my
programming team at work is a whole two people, and I'm the lead programmer,
it pretty much works that way there, too <grin>).
Rob
Robert Morley
> "tblPreferences",
Ummm...apparently I'm just randomly dropping words from sentences. That was
supposed to read "I currently have a view", of course. <blush>
Rob
onedaywhen
Robert Morley wrote:
> Like I said, for myself, I don't generally stick to simply "tbl" or "vw" or
> whatever unless there's no other logical prefix
He's a idea for you: if there is no *logical* prefix (i.e. the table's
name already conveys meaning) then don't use a prefix that conveys its
*physical* implementation. At best it's redundant, worse it's mixing
logical and physical models.
Jamie.
--
onedaywhen
BruceM wrote:
> I learned about a naming
> convention that makes sense, so I started using it, and found it to be
> helpful.
Did you browse the whole 'naming conventions' aisle before making your
choice?
> It's interesting how much of the discussion is about how using one system or
> another will appear in the eyes of somebody else.
> Did you choose the
> naming convention you use because you "wannabe" like somebody else?
Of course. When I started dabbling in WordBasic, I used logical terms
for variable names; I was working in the office of the board of
directors and wore a blazer, slacks and smart shiny shoes to the
office.
I wanted to move into VBA software development so, to be taken
seriously by what I hoped would be my peers, I adopted the naming
convention lng- for Long Integer, str- for String, dtm- for Date, etc.
These prefixes did not add value for me personally: the variable name
told me how I was using it and hence conveyed the data type, the prefix
seemed to be redundant and jar.
Luckily in my SQL career I took the 'standard SQL' path from day one
and never got into the whole 'tbl', 'fields' and other Access
conventions. When I first started using .NET I was very relieved that
the convention has moved away from data type prefixes to logical
variable names; I still use the prefixes in VBA, though sometimes I
can't be bothered and stick with the .NET convention. I was amused the
other day to see an MVP using camelCase for their Access column -
sorry, field - names <g>.
And, yes, I use the standard SQL terms column (in place of 'field'),
VIEW (in place of 'Query'), 'DECIMAL' (in place of 'Currency' <g>), a
relational/industry standard key (in place of 'autonumber' <vbg>) to
encourage others to look outside of the Access ghetto in the hope they
may benefit as I have.
Oh and today I'm wearing a t-shirt and jeans (because sometimes you
need to fit in) with walking boots (because sometimes you need to do
what makes practical sense).
Jamie.
--
Vayse
> programming style, they have now reverted completely, and prefixes are
> AbsolutelyOut for anything based on the new versions of VisualStudio --
> or should that be visualStudio?
>
I have to do a lot of vb.net work, and I find the new naming convention a
bit awkard. New MS articles insist on dropping the o for object, as
everything is an object now. And cls for class is gone as well, of course.
But there are still some 2003 and older articles amongst the help files,
where they have different naming conventions. But thats not too bad, its
easy enough to get used to.
My old way: Dim oClient as New clsClient
New way: Dim SalesClient as New Client
It does mean thinking of names though! Like having to use SalesClient in
this sample.
What really gets me is the names on forms. So instead of gridClients, its
now ClientsDataGridView. And then theres ClientNameTextBox. Its very hard to
get used to, after years of txtClientName!
In some ways it makes sense. All your Client objects are grouped together.
But I think I'll keep to the old ways in Access. Though I may not be able to
keep up two naming conventions for ever.
Diarmuid
david epsom dot com dot au
used for formulas, where i,j, and k were indexes to an Array. This
was a notation that predated computers.
We got the word 'array' from the same source: if FORTRAN had not
been used by engineers and mathematicians, we probably would have
had a 'table' instead, which is what we now call that construct
when we use Access to save it to disk and read it back again.
(david)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uqvv8fcp...@TK2MSFTNGP05.phx.gbl...
david epsom dot com dot au
on separate tabs. If they were all listed together, it
would make sense to totally drop the annotation: but
I share multiple databases with other developers, and
when you are looking for a view, you need to know if you
are in the wrong database, or just on the wrong page.
The terms 'field' and 'record' encourage me to think
in terms of card databases. I know that they were also
applied to file systems and sequential processing, but
they were never any more relevant there than they are
with SQL databases and set-based theory. Even the word
'file' was a barrier to people being introduced to 'file
systems': the concepts were too dissimilar to be helpful.
If they don't help with SQL, it's not because they are
bound to file/tape concepts: they never really worked
there either.
However, to be honest, you can use any sound to refer
to any concept. The real problem is that people use
language to define social groups, and social groups are
defined just as much by who you can exclude as by who
you include.
(david)
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
news:1152785248.5...@m73g2000cwd.googlegroups.com...
david epsom dot com dot au
all the features that made BASIC - fully memory managed string
class, interpreted library, single basic type, etc - dumped the
ideas like prefix notation that were only there to make up for
defects in the C language definition - and given us a new C
language with all the features they laughed at.
In a language that supports memory management and ByRef variables,
where the most difficult part of programming is not keeping track
of your indirection, stack pointers, and string space, it no
longer makes sense to put the syntax annotations in the high-
visibility space at the front of the variable name.
Now, after a long detour through C, we are back were we were
with Pascal: variable names refer to the logical content of
the variable, rather than the structure of the variable.
I still use postfix notation for variable types: name$, cost%
where it helps. There should be postfix annotations for all
types /where it matters/. Unfortunately, that is one place where
.Net is still caught in the 'real-programmer' fashion. They
can't use language-supported postfix notation because C didn't
do it that way, and because BASIC did.
Instead, when they need to note information about the structure
of the element, they are typing it out, COBOL style. And it's
not that I object to COBOL: I could do object-oriented
programming in COBOL, even though COBOL was not 'object-oriented',
and you can do postfix notation in .NET, even though the language
does not support it, but in the end, it's more efficient to
have language support for features like that rather than doing
it all your self.
(david)
"Vayse" <va...@deadspam.com> wrote in message
news:OCaoZ9x...@TK2MSFTNGP05.phx.gbl...
BruceM
"onedaywhen" <jamiec...@xsmail.com> wrote in message
news:1152865021.3...@s13g2000cwa.googlegroups.com...
>
> BruceM wrote:
>> I learned about a naming
>> convention that makes sense, so I started using it, and found it to be
>> helpful.
>
> Did you browse the whole 'naming conventions' aisle before making your
> choice?
I picked one that seemed to be in common use, and turned my attention to the
program.
>
>> It's interesting how much of the discussion is about how using one system
>> or
>> another will appear in the eyes of somebody else.
>> Did you choose the
>> naming convention you use because you "wannabe" like somebody else?
>
> Of course. When I started dabbling in WordBasic, I used logical terms
> for variable names; I was working in the office of the board of
> directors and wore a blazer, slacks and smart shiny shoes to the
> office.
>
> I wanted to move into VBA software development so, to be taken
> seriously by what I hoped would be my peers, I adopted the naming
> convention lng- for Long Integer, str- for String, dtm- for Date, etc.
> These prefixes did not add value for me personally: the variable name
> told me how I was using it and hence conveyed the data type, the prefix
> seemed to be redundant and jar.
When learning something new we tend to adopt methods used by those from whom
we learn. As it happens I learned from people who tended to use a
particular naming convention.
>
> Luckily in my SQL career I took the 'standard SQL' path from day one
> and never got into the whole 'tbl', 'fields' and other Access
> conventions. When I first started using .NET I was very relieved that
> the convention has moved away from data type prefixes to logical
> variable names; I still use the prefixes in VBA, though sometimes I
> can't be bothered and stick with the .NET convention. I was amused the
> other day to see an MVP using camelCase for their Access column -
> sorry, field - names <g>.
>
> And, yes, I use the standard SQL terms column (in place of 'field'),
> VIEW (in place of 'Query'), 'DECIMAL' (in place of 'Currency' <g>), a
> relational/industry standard key (in place of 'autonumber' <vbg>) to
> encourage others to look outside of the Access ghetto in the hope they
> may benefit as I have.
>
> Oh and today I'm wearing a t-shirt and jeans (because sometimes you
> need to fit in) with walking boots (because sometimes you need to do
> what makes practical sense).
Perhaps it is or will be my downfall that I have never had much interest in
"fitting in". My choices are logical and systematic, and they work for me.
If they make me uncool in the eyes of some, so be it.
>
> Jamie.
>
> --
>
onedaywhen
BruceM wrote:
> > Did you browse the whole 'naming conventions' aisle before making your
> > choice?
>
> I picked one that seemed to be in common use
>
> "fitting in". My choices are logical and systematic, and they work for me.
> If they make me uncool in the eyes of some, so be it.
Is there anything more conventional than choosing the convention in
common use <g>?!
Maybe you are less of a rebel than you think ;-)
Jamie.
--
BruceM
>
> BruceM wrote:
>> > Did you browse the whole 'naming conventions' aisle before making your
>> > choice?
>>
>> I picked one that seemed to be in common use
>>
>> Perhaps it is or will be my downfall that I have never had much interest
>> in
>> "fitting in". My choices are logical and systematic, and they work for
>> me.
>> If they make me uncool in the eyes of some, so be it.
>
> Is there anything more conventional than choosing the convention in
> common use <g>?!
To quote from (I think) one of your earlier posts: "If you want to appear
as a 'serious' SQL database type person, take a look at what people do
outside of the Access ghetto". If people are going to evaluate my Access
abilities I hope they will do so based on what I do with the program. If
the convention in common use helps me be understood it has done what I ask
from a convention.
>
> Maybe you are less of a rebel than you think ;-)
Oh, I can fit into a variety of situations. But as always, any choice will
be embraced by some and held at arm's length by others.
Interesting as always to engage in these exchanges with you.
Regards, Bruce
>
> Jamie.
>
> --
>
Nikos Yannacopoulos
> And, yes, I use the standard SQL terms column (in place of 'field'),
> VIEW (in place of 'Query'), 'DECIMAL' (in place of 'Currency' <g>), a
> relational/industry standard key (in place of 'autonumber' <vbg>) to
> encourage others to look outside of the Access ghetto in the hope they
> may benefit as I have.
Jamie / onedaywhen,
It looks to me like you are consistently overlooking a simple fact:
these here are ACCESS newsgroups, and people come here with their Access
questions... therefore, the replies are in Access terms. I'll accept,
for the sake of the argument, that "view" is correct, and "query" is
wrong, bad, filthy or whatever else you want it to be... even so, what
makes you so confident that the poster of a question will actually know
what you mean by "view"? How can you be so sure they won't be further
confused instead?
I truly admire the depth of your knowledge of SQL, which is exactly why
I hate to see your energy wasted on pointless arguments. I suppose you
must be enjoying this more than actually helping others!
Nikos
onedaywhen
Nikos Yannacopoulos wrote:
> It looks to me like you are consistently overlooking a simple fact:
> these here are ACCESS newsgroups, and people come here with their Access
> questions... therefore, the replies are in Access terms. I'll accept,
> for the sake of the argument, that "view" is correct, and "query" is
> wrong, bad, filthy or whatever else you want it to be...
I'll settle for 'non-standard'.
> even so, what
> makes you so confident that the poster of a question will actually know
> what you mean by "view"? How can you be so sure they won't be further
> confused instead?
I get your point. I'm pretty sure that when I use the term 'view' I
usually say 'Query', 'query object' or similar.
Access-specific terms can similarly be confusing for someone thinking
in standard SQL terms e.g. a 'delete query' would be an oyxmoron.
Surely as a community we can change the prevailing conventions and
bring terminology closer in line with that of the wider SQL world. In
the long run that would reduce confusion, I think.
To use (yet another) example, years ago I was sent on an elementary
Access course. The tutor was trying to teach about relationships 1:1,
1:m and m:m. I came out utterly confused, not knowing whether this
terminology applied to Query objects or Table objects or what. I didn't
use Access after the course and promptly forgot everything. When I
later started using SQL on another platform I quickly encountered
primary keys and foreign keys, of course, and it suddenly struck me
that this is what that tutor had meant! So for me, the SQL syntax made
sense and the Access approach did not. I figure there's got to be
others like me out there who are confused by the Access conventions but
would benefit from seeing the bigger picture; also lots of people for
whom it would be overwhelming, I guess.
> I hate to see your energy wasted on pointless arguments
Touché <g>! I'm only as bad as everyone else posting to a thread with
the phrase 'pointless debate' in the subject line ;-)
Jamie.
--
BruceM
"onedaywhen" <jamiec...@xsmail.com> wrote in message
news:1152890439....@i42g2000cwa.googlegroups.com...
Nikos Yannacopoulos wrote:
> It looks to me like you are consistently overlooking a simple fact:
> these here are ACCESS newsgroups, and people come here with their Access
> questions... therefore, the replies are in Access terms. I'll accept,
> for the sake of the argument, that "view" is correct, and "query" is
> wrong, bad, filthy or whatever else you want it to be...
I'll settle for 'non-standard'.
> even so, what
> makes you so confident that the poster of a question will actually know
> what you mean by "view"? How can you be so sure they won't be further
> confused instead?
I get your point. I'm pretty sure that when I use the term 'view' I
usually say 'Query', 'query object' or similar.
The Access term is widely understood in an Access newsgroup.
Access-specific terms can similarly be confusing for someone thinking
in standard SQL terms e.g. a 'delete query' would be an oyxmoron.
Those people probably won't come to an Access newsgroup.
Surely as a community we can change the prevailing conventions and
bring terminology closer in line with that of the wider SQL world. In
the long run that would reduce confusion, I think.
Most people come here to learn about solving specific problems, and do not
care about the world outside of Access. While you may be correct about
using terminology that is in wide usage, most people posting answers here
probably do not wish to add a terminology tutorial to their responses, nor
are those posting questions likely to be interested in such instruction.
To use (yet another) example, years ago I was sent on an elementary
Access course. The tutor was trying to teach about relationships 1:1,
1:m and m:m. I came out utterly confused, not knowing whether this
terminology applied to Query objects or Table objects or what. I didn't
use Access after the course and promptly forgot everything. When I
later started using SQL on another platform I quickly encountered
primary keys and foreign keys, of course, and it suddenly struck me
that this is what that tutor had meant! So for me, the SQL syntax made
sense and the Access approach did not. I figure there's got to be
others like me out there who are confused by the Access conventions but
would benefit from seeing the bigger picture; also lots of people for
whom it would be overwhelming, I guess.
The problem was, of course, with the instructor. I am aware of your disdain
for Access, but an instructor's competence or lack thereof is not a
reflection of the software. A friend of mine does development work with
Filemaker. He is similarly disdainful of Access (and all things Microsoft),
but I hear about his struggles with problems that could easily be solved
with an After Update event or something of the sort. Still, he won't even
consider any possibility other than that Access is at the bottom of the
database scrap heap.
> I hate to see your energy wasted on pointless arguments
Touché <g>! I'm only as bad as everyone else posting to a thread with
the phrase 'pointless debate' in the subject line ;-)
Yes, quite a number of us like to join the fray, don't we?
Jamie.
--
Robert Morley
that are unrelated to anything else at least get lumped together. I
personally find that easier than having them all scattered about when
looking at an alphabetical list. To each his or her own, though.
Rob
"onedaywhen" <jamiec...@xsmail.com> wrote in message
news:1152863366.5...@p79g2000cwp.googlegroups.com...
BruceM
"BruceM" <bam...@yawwhodawtcalm.not> wrote in message
news:ugY2121...@TK2MSFTNGP05.phx.gbl...
Robert Morley
While I can hardly argue the point that this is an Access newsgroup ("like,
duh!" <a la stereotypical teenage girl>), one of the groups that this is
being posted to is also geared towards ADP and SQL Server, and so is likely
to attract at least a few so-called "real" database programmers who are
"just trying to help the poor misguided souls who seem to think that Access
is actually worth mentioning".
Typically, the poor misguided souls who seem to think that they're using a
"real" database system and Access users are not fall into one of two
categories: those who believe that Access is not as robust as <insert
Enterprise-level system here>, and those who have an unreasoning prejudice
against anything that isn't their chosen system.
Now, the former group certainly has a point. Access is not as robust of a
back-end as SQL Server (or Oracle or whatever else) is. I know it may
violate your preconceived notions, but not every database needs to be run on
an enterprise-level DBMS. Access is meant to create small, portable
databases on a local computer, or maybe with replication under a limited set
of circumstances, and it does that very well. And as a front-end, frankly,
I've yet to see its match. .NET may give you a lot more programming
options, and make the programming tier very powerful and easy to write, but
have you honestly ever tried designing a form with it? GODS what a
nightmare!
And as to the latter group, there's nothing we can do about them short of
chaining them to a desk with only Access available and forcing them to
actually USE it for more than a few hours and to actually learn how it works
and what it's capable of...not to mention forcing them into learning a
different way of thinking that doesn't involve command-line interfaces for
half the work they do. (Why am I reminded of Linux users?)
Alright, so much of the above is fairly prejudicial, but honestly people,
these are the attitudes you're projecting...why SHOULDN'T I poke fun at you?
Rob
aaron...@gmail.com
don't ask questons; just do it
'standing on the shoulders of giants'
it is very useful; also when writing classes you start them with a C.
if you have standards like this; it is very very easy to automate
access if you follow standards like this.
Robert Morley wrote:
> Because my professional pride hates being insulted by being called an
> amateur even more than I hate being drawn into pointless debates. :)
>
>
> Rob
>
> "BruceM" <bam...@yawwhodawtcalm.not> wrote in message
> news:uid3uTcp...@TK2MSFTNGP03.phx.gbl...
> > If the debates are pointless, why are you adding to them? Name things as
> > you choose. For myself it would be confusing if account-related tables,
> > queries, forms, and reports all start with the same prefix, but that's
> > just my preference.
David F. Cox
and onto str_A and Int_I .
The word "fashion" comes to mind.
I now try to write my programs for those who are going to read them.
David F. Cox
"Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message
news:uhstMLcp...@TK2MSFTNGP05.phx.gbl...
>I still don't buy that this is either product-specific or amateur-specific.
>I've seen countless numbers of experienced, formally trained (and who
>learned it on their own) database programmers on every platform I've ever
>used who use "tbl" for tables, "vw" for views, "usp" for user stored
>procedures, not to mention "frm" for forms, "rpt" for reports, etc., ad
>nauseum.
>
> Personally, I don't see the point of starting ALL objects in a category
> with the same letters; that's generally redundant (though occasionally
> useful when you're trying to distinguish between views and tables, etc.).
> My general preference is to base it around the function of the object.
> "acct" for account-related tables, "resp" for respondent-related tables,
> "list" for simple lookup-type tables, etc. Only for objects that don't
> have a significant inter-relationship with the rest of the database (i.e.,
> localization tables, user preferences, etc.) do I use the generic "tbl",
> "frm", or whatever.
>
> And while "tbl" itself may have first appeared in a Smart Access article
> in 1993, as I said, Hungarian notation itself pre-dates that. As I said,
> Simonyi Károly (aka Charles Simonyi) did indeed work at Microsoft, but he
> started Hungarian Notation back when he was working for Xerox. It's
> hardly a surprise that it later appeared in a Microsoft product that he
> worked on. Hell, ignore Hungarian notation for a moment, how long ago did
> people start using "i" for integer? "For i = " was one of the first
> constructs I learned almost 30 years ago.
>
>
> Rob
>
> "Craig Alexander Morrison" <c...@microsoft.newsgroups.public.com> wrote in
> message news:ulTN4sYp...@TK2MSFTNGP03.phx.gbl...
>>> Oh and just for the record, Hungarian Notation became popular with
>>> languages like VB/VBA, but actually pre-dates it. The inventor, Simonyi
>>> Károly, was working for Xerox at the time and only much later did he
>>> move
>>> to Microsoft.
>>
>> For the record "tbl" and other such fripperies first appeared in a 1993
>> Smart Access article and has subsequently appeared in the ADH books. I
>> don't
>> mind one using tags in code but not for database objects. Charles Simonyi
>> actually worked on Access 1; I am not sure what he thinks of the
>> Lesynski/Reddick extensions.
>>
>> It is a good sign of an amateur with limited experience of other
>> products.
>> Some amateurs are very good programmers though.
>>
>> Nearly all formally trained Relational (or SQL) Database designers would
>> find this "tbl" tag laughable.
>>
>> --
>> Slainte
>>
>> Craig Alexander Morrison
>> Crawbridge Data (Scotland) Limited
>>
>> "Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in message
>> news:%237AzQEW...@TK2MSFTNGP03.phx.gbl...
>>> Like I said, different ways of thinking. I look at most of your points
>>> and disagree with them either in part or in whole, but frankly, this
>>> isn't
>>> the place to get into this kind of discussion. The original post has
>>> been
>>> answered with two different solutions, and that's the end of it as far
>>> as
>>> I'm concerned. I just bitched someone else out in another NG for
>>> exactly
>>> this kind of "mine is bigger than yours" discussion that serves no
>>> purpose
>>> but to bicker pointlessly. Everybody's got their favourite apps and the
>>> apps they think are toys, we simply disagree on which ones are which.
>>>
>>> :)
>>>
>>>
>>>
>>> Rob
>>>
>>> "Tim Ferguson" <Fergu...@softhome.net> wrote in message
>>> news:Xns97FDE698BF5F...@207.46.248.16...
>>>> "Robert Morley" <rmo...@magma.ca.N0.Freak1n.sparn> wrote in
>>>> news:uu9k#kPpGH...@TK2MSFTNGP05.phx.gbl:
>>>>
>>>>> Obviously someone thought so, or they never would've designed combo
>>>>> box lookups to work in tables.
>>>>
>>>> There are several things in Access that relegate it into the "toy"
>>>> platform in the eyes of other database developers on "real" systems.
>>>> The
>>>> quaint but misguided fashion for putting "tbl" in front of object names
>>>> is one; the presence of the "look up field" is another. I regret this
>>>> because when you get up close, Jet is a pretty fine database engine and
>>>> Access is a flexible and usable rapid development platform, but it will
>>>> continue to get a rotten press as long as it's aimed at the Janet and
>>>> John level of user. The type of users, in effect, that get drowned in
>>>> any
>>>> case as soon as they step off the dumb-spreadsheet kind of appliction.
>>>>
>>>> FWIW, it seems that the Access-as-toy party has won the debate because
>>>> Jet development is being taken over by the Access UI team. I think it's
>>>> time to be off to MySQL before they put in the paper clip telling you
>>>> not
>>>> to put financial data into an integer field.
>>>>
>>>>> What it really comes down to is that each of us has our own opinions
>>>>> and ways of doing things. Don't get upset with someone just because
>>>>> they think and work differently than you do.
>>>>
>>>> I get upset because of two things. Firstly, posts like yours may be
>>>> seen
>>>> by people who know about databases but not much about Access, who will
>>>> merely have their suspicions confirmed that access is a plaything for
>>>> people who don't know their way round Codd or Date. The second reason
>>>> is
>>>> that they may be seen by people who don't know much about Access or
>>>> databases, and who will then think this is a good and reasonable way to
>>>> use it; and whose horizons will forever be shortened.
aaron...@gmail.com
of a problem
being able to call a view tbl is a great feature; it's a life saver--
you should only ever use it like once or twice a year.. but it's
definitely a great feature
you can't always predict how things are going to unfold
having a strict naming convention is a requirement not an afterthought
onedaywhen
Robert Morley wrote:
> I currently [have a] view called "tblPreferences",
Until now I considered the 'tbl' prefix to merely redundant, other than
to identify an Access designer. Thanks to this usage example, I now see
it is a potential be a hindrance i.e. when you change your base table
into a virtual table (VIEW/Query) you have to either find and change
the name in every reference in code or make everyone live with a
counterintuitive prefix and those people who claim they find the 'tbl'
to be helpful are going to be really confused/annoyed <g>.
If that doesn't convince you that the 'tbl' prefix is to be avoided I'm
not sure what will.
Actually, there is something else nobody has mention AFAIK and that is
ISO 11179-5 'Naming and Identification Principles for Data Elements'
- google it. I urge anyone follow the ISO naming convention and see
if you still think you need the 'tbl' prefix.
Readers of this thread may find this article interesting:
http://www.dbazine.com/db2/db2-disarticles/gulutzan5
"VBA programmers use a Hungarian-style convention (Leszynski/Reddick),
so tblEmployees is a normal name. [However,] SQL programmers have less
need for Hungarian Notation because they can get the type information
from the metadata tables. Data in relational databases -including
metadata - should be atomic and shouldn't be redundant...'This is the
year 2000; the '60s are over! Things like "str_firstname" or
"tblPayroll" are redundant.' -- Joe Celko"
Jamie.
--
BruceM
"onedaywhen" <jamiec...@xsmail.com> wrote in message
news:1153126169.6...@35g2000cwc.googlegroups.com...
>
> Robert Morley wrote:
>> I currently [have a] view called "tblPreferences",
>
> Until now I considered the 'tbl' prefix to merely redundant, other than
> to identify an Access designer. Thanks to this usage example, I now see
> it is a potential be a hindrance i.e. when you change your base table
> into a virtual table (VIEW/Query) you have to either find and change
> the name in every reference in code or make everyone live with a
> counterintuitive prefix and those people who claim they find the 'tbl'
> to be helpful are going to be really confused/annoyed <g>.
>
> If that doesn't convince you that the 'tbl' prefix is to be avoided I'm
> not sure what will.
So you don't need to change the name if you switch from a table to a query,
but what if you change from one query to another? Also, several queries
could be based on the same table. Which one has the same name as the table?
What names are the others given?
>
> Actually, there is something else nobody has mention AFAIK and that is
> ISO 11179-5 'Naming and Identification Principles for Data Elements'
> - google it. I urge anyone follow the ISO naming convention and see
> if you still think you need the 'tbl' prefix.
I tried the search. That ISO standard is about metadata, which I do not
claim to understand, but which is well beyond the interests of most people
posting questions here. In looking through some of your links I have seen
calls for separating all words by spaces, and for not using spaces at all;
for using uppercase, lowercase, and mixed case; for using singular and
plural; and so forth. There is an expert somewhere with 25 years experience
or whatever who no doubt disagrees with you. Who cares, unless you are both
on the same project? If I work on a project with several others, the
project leader will come up with a naming convention, which I will follow.
I don't need to agree, just to get the work done. If I am that project
leader some day, by then I will have refined my views on the subject.
>
> Readers of this thread may find this article interesting:
>
> http://www.dbazine.com/db2/db2-disarticles/gulutzan5
>
> "VBA programmers use a Hungarian-style convention (Leszynski/Reddick),
> so tblEmployees is a normal name. [However,] SQL programmers have less
> need for Hungarian Notation because they can get the type information
> from the metadata tables. Data in relational databases -including
> metadata - should be atomic and shouldn't be redundant...'This is the
> year 2000; the '60s are over! Things like "str_firstname" or
> "tblPayroll" are redundant.' -- Joe Celko"
>
> Jamie.
Interesting as always.
Best regards.
>
> --
>
onedaywhen
BruceM wrote:
> So you don't need to change the name if you switch from a table to a query,
> but what if you change from one query to another?
If the logical name still stands, change the definition of the
query/view if it is more conducive to do so e.g. to point the first
query at the second query. If the logical meaning changes then you will
have to change the name in code anyhow; prefixes based on physical
attributes will neither help nor hinder.
> several queries
> could be based on the same table. Which one has the same name as the table?
Remember a query/view is a (virtual) table. Two tables cannot have the
same name.
> What names are the others given?
Choose names that differentiate their logical differences. I admit that
the differences can sometimes be so subtle to be tempted to resort to
using metadata (I'm thinking now about the kind of scenario where you
want to remove all permissions from the base table to be able to
control SQL DML via stored procedures ('parameterized query objects').
> That ISO standard is about metadata, which I do not
> claim to understand, but which is well beyond the interests of most people
> posting questions here.
The 'tbl' prefix is definitely metadata. The 'tbl' prefix is definitely
not recommended by the ISO standard.
You could be right that people in these groups are not interested in
creating data dictionaries but I think it is in their interests to do
so and I think the ISO 11179 can help, even if you decided to deviate
from that prescribed (as Joe Celko does for his naming convention
descibed in his excellent book, 'SQL Programming Style').
Jamie.
--
Robert Morley
I tend to avoid the "tbl" prefix.
Thanks to the wondrous concepts of n-tier development, however, the impacts
of changing the view name outside the database, however, are minimal. As I
said, I just haven't gotten around to it yet. (And due to the fact that the
database is in high demand right now, I probably won't get around to it
until Christmas.)
Rob
BruceM
>
> BruceM wrote:
>> So you don't need to change the name if you switch from a table to a
>> query,
>> but what if you change from one query to another?
>
> If the logical name still stands, change the definition of the
> query/view if it is more conducive to do so e.g. to point the first
> query at the second query. If the logical meaning changes then you will
> have to change the name in code anyhow; prefixes based on physical
> attributes will neither help nor hinder.
>
>> several queries
>> could be based on the same table. Which one has the same name as the
>> table?
>
> Remember a query/view is a (virtual) table. Two tables cannot have the
> same name.
My mistake. But what is your point about using a query instead of a table
in the code?
>
>> What names are the others given?
>
> Choose names that differentiate their logical differences.
I do that, and will continue to do so.
> I admit that
> the differences can sometimes be so subtle to be tempted to resort to
> using metadata (I'm thinking now about the kind of scenario where you
> want to remove all permissions from the base table to be able to
> control SQL DML via stored procedures ('parameterized query objects').
I use naming conventions because it's easier that way. I will agree that
for tables and queries a prefix may be redundant, but any naming scheme
follows certain rules.
>
>> That ISO standard is about metadata, which I do not
>> claim to understand, but which is well beyond the interests of most
>> people
>> posting questions here.
>
> The 'tbl' prefix is definitely metadata. The 'tbl' prefix is definitely
> not recommended by the ISO standard.
ISO 11179 is listed thus: Information Technology -- Metadata Registries
(MDR). That was my point about the ISO standard.
>
> You could be right that people in these groups are not interested in
> creating data dictionaries but I think it is in their interests to do
> so and I think the ISO 11179 can help, even if you decided to deviate
> from that prescribed (as Joe Celko does for his naming convention
> descibed in his excellent book, 'SQL Programming Style').
Ah, those people who don't know what's good for them. Pesky bunch, aren't
we?
;-)
>
> Jamie.
Best regards.
>
> --
>
onedaywhen
BruceM wrote:
> ISO 11179 is listed thus: Information Technology -- Metadata Registries
> (MDR). That was my point about the ISO standard.
I was referring to ISO/IEC 11179-5: 2005(E) Information Technology -
Metadata Registries (MDR) - Part 5: Naming and identification
principles
Put simply, metadata is data about data e.g. data = #2006-07-17#,
metadata = 'current date', metadata = 'salary start date'. So, ISO
11179-5 can help you name your tables, columns, etc.
Jamie.
--
david epsom dot com dot au
>
> Robert Morley wrote:
>> I currently [have a] view called "tblPreferences",
>
> Until now I considered the 'tbl' prefix to merely redundant, other
> than to identify an Access designer. Thanks to this usage example,
> I now see
It does identify an Access designer.
To an Access designer the information is not redundant.
The information would be redundant if there were no
difference between 'query' views and 'table' views.
The fact that there is a difference between 'table' views
and 'query' views is a failure to implement the relational
model in Access. I note that this is a common failure in
'relational' database systems.
It's a fact that the 'tables' and 'queries' are listed
on separate tabs in Access. If they were all listed together,
it would make sense to totally drop the annotation: but
I share multiple databases with other developers, and
when you are looking for a view, you need to know if you
are in the wrong database, or just on the wrong page.
But given that SQL Server also lists 'tables' and 'views'
separately, what is different about Access that makes the
metadata annotation useful to Access programmers, but not
useful to SQL Server DBAs?
Well 'duh': Access programmers use the GUI for reference
when building forms, reports and queries. If you aren't
using the GUI as your primary reference, the tab location
of the view is not relevant to your naming convention.
What about Joe Celko? If I may quote:
"Please post DDL, so that people do not have to guess"
Obviously, Joe C is not referencing the Access GUI for
metadata, so in his case, using 'tbl' and 'qry' annotations
would be redundant.
(david).
onedaywhen
david epsom dot com dot au wrote:
> What about Joe Celko? If I may quote:
> "Please post DDL, so that people do not have to guess"
> Obviously, Joe C is not referencing the Access GUI for
> metadata, so in his case, using 'tbl' and 'qry' annotations
> would be redundant.
When people post their questions in the Access groups, do you want to
see, 'I have this table, it looks like this' and for them to attach a
screenshot of the Access GUI? I think SQL DDL is best way to describe a
table, even for Access e.g. non-speakers of SQL can still get the
general idea.
Jamie.
--
david epsom dot com dot au
A potential hindrance for people posting DDL in
Access groups?
No, we are also talking about the use of naming
conventions in building databases.
Once we move away from the news groups, the fact
remains that annotated names have a functional
value for Access programmers, not seen by other
DBA's, and are not merely a social marker.
The naming convention does have some drawbacks:
for other kinds of users it might only have
drawbacks, but for Access programmers it is an
indication that they understand their tool, not
an indication that they fail to understand other
tools.
Incidently, for an Access programmer, there are
two conflicting naming criteris: (1) The names
should sort logicaly in the database window,
(2) the names should be short enough to display
intelligently in the default QBE design view.
Criteria 1 encourages coding repetitive information
at the start of the name, so that related views
group together:
Criteria 2 encourages coding repetitive information
at the end of the name, so that differentiating
information is easily visible in the QBE design
window.
MS made a first attempt to handle these problems
with the 'Groups' tab in Access 2000, but in
practice it's always difficult: Note how the
MS file system has progressed from 'shortcuts'
to 'hard links' to 'soft links', which is an
attempt to address a similar problem.
(david)
"onedaywhen" <jamiec...@xsmail.com> wrote in message
news:1153207578....@p79g2000cwp.googlegroups.com...
onedaywhen
david epsom dot com dot au wrote:
> "I now see it is a potential be a hindrance"
>
> A potential hindrance for people posting DDL in
> Access groups?
No a potential hindrance when replacing a base table with a VIEW/Query.
No biggie, though, I admit.
> Once we move away from the news groups, the fact
> remains that annotated names have a functional
> value for Access programmers, not seen by other
> DBA's, and are not merely a social marker <<snipped>>
I'm still not convinced. On a non-Access platform, the user can look in
the schema information tables; for an Access user, they can
*additionally* look in the Access GUI. If all the Access user needs to
do is switch between tabs, I would have though there would be *less*
need for a prefix!
aaron kempf makes one of his rare good points (though it may been
unintentional in this case <g>): for some users you do want to 'trick'
them into thinking a VIEW/Query is a base table. The DBAs should be
intimate enough with the schema to know where to look without hints
<g>.
Jamie.
--
david epsom dot com dot au
the schema, but I still don't remember all of the names:
I know MM_Audit and DP_Contract, but am I looking for
SystemUsers, or Users?
If I know that all of my family live in the big tower
with 100 apartments, and you know that all your family
live in a 4000 bed dormitory, I will have less trouble
finding the kids after finding the correct apartment.
But the apartment number will be very helpful.
All you have to do is stand in the middle of the room
and look around. Once you've found your wife, you will
have to stand in the middle of the room and look for
your kids: are they over by the TV? Are they over by
the coffee?
The fact that all my family are all on the 'same tab'
doesn't make the tab identifier less helpful: the tab
identifier is helpful because there are multiple tabs,
and the multiple tabs are in use.
If there were no functional difference between queries
and tables, there would be no value to the annotation.
But in Access there is a functional difference, and there
is a functional value to the Access programmer.
One of the original ideas of relational database design
was to eliminate the functional difference between
tables and queries, so that a view could be inverted
without cost. In that sense, Access is fairly successful.
(If it was totally successful, OLAP cubes would not be
required)
But in the GUI it retains the functional distinction
between queries and tables, and that has implications
for useful naming conventions.
(david)
"onedaywhen" <jamiec...@xsmail.com> wrote in message
news:1153218219.3...@35g2000cwc.googlegroups.com...
onedaywhen
david epsom dot com dot au wrote:
> I have more than 4000 views. I am fairly intimate with
> the schema, but I still don't remember all of the names:
> I know MM_Audit and DP_Contract, but am I looking for
> SystemUsers, or Users?
Wow, that's a staggering amount.
I've read anecdotally that the rule of thumb for the maximum number of
tables in a SQL database is about a hundred. The most complex data
model I know of, that being developed for the UK Nation Health Service,
had around 200 classes last time I checked.
I'm puzzled how you could have a need for so many views/queries. You
have several thousand distinct entity types? Many legacy apps to
support? And you are not sure the sheer number is not a maintenance
issue...?
Jamie.
--
Robert Morley
> schema to know where to look without hints <g>.
That applies very well to the person who designed the database, but what
about the DBA who simply has someone else's work dumped into his lap?
Rob
Robert Morley
> All you have to do is stand in the middle of the room
> and look around.
Unless they've left the apartment, of course, which sorta goes back to the
point of converting a table to a view for whatever reason. :) (Okay, so I'm
arguing against my own points here, but I acknowledge that just about ANY
naming convention system has flaws.)
Rob
Robert Morley
never use them to represent entities...tables should do that in their own
right, not views). Sometimes a view is useful simply to sort data in a
certain order; for end users to export data to Excel or Word (sticking
within the context of MS products for the moment); sometimes it's easier to
construct and maintain complex views by splitting them into several smaller
ones, even though the smaller ones will never be used outside the context of
the larger one; sometimes a view may be used entirely internally by the DBA
to "view" only the relevant information from one or more tables, rather than
going to the base tables themselves.
Others argue that all these sorts of things should be table-valued functions
or SPs, but whatever your choice in THAT argument, it works out the same in
the end...you're using SOMETHING to fulfill all these various needs (unless
you're a proponent of writing SQL code in your front-end, of course, but
let's stick with n-tier development for now).
For myself, I have just shy of 100 tables in my database, so it stands to
reason that I would have even more views (close to 500). And I don't
consider my data model to be HUGELY complex...certainly not the simplest
thing in the world, but not the most complex, either. I can see how a data
model with several hundred tables could easily end up with thousands of
views for various purposes.
Rob
"onedaywhen" <jamiec...@xsmail.com> wrote in message
news:1153229181.5...@m73g2000cwd.googlegroups.com...
onedaywhen
Robert Morley wrote:
> I almost
> never use [views/queries] to represent entities...tables should do that in
> their own right
Not necessarily.
Here's a usage example where exposing an entity via a view/query is
useful in Access. Say you wanted to maintain a 'date inserted' column
for a table:
CREATE TABLE MyTable (
key_col INTEGER NOT NULL UNIQUE,
data_col INTEGER NOT NULL,
effective_date DATETIME DEFAULT NOW() NOT NULL
);
Obviously you don't want the effective_date column to be written to
directly e.g. a user could accidentally (or otherwise) enter a far
future date; let's also say this metadata need not be exposed either,
so you wrap it in a view/query:
CREATE VIEW MyFakedTable
AS
SELECT key_col, data_col
FROM MyTable;
Revoke all permissions from the table and grant them to the view/query.
Users can now insert into the view/query and the default 'timestamp'
gets automatically applied:
INSERT INTO MyFakedTable (key_col, data_col) VALUES (1, 1);
Of course, you are more likely to want a 'last modified' date i.e. the
effective_date column is additionally maintained for updates. For
Access, a stored proc/parameterized query is required e.g.
CREATE PROCEDURE SetMyTableData (
arg_key_col INTEGER,
arg_data_col INTEGER
) AS
UPDATE MyTable
SET data_col = arg_data_col,
effective_date = NOW()
WHERE key_col = arg_key_col;
Obviously you would now revoke update permissions from the view/query.
Note that on other platforms there may be alternatives to procs e.g. in
SQL Server updates can be controlled via an INSTEAD OF trigger on the
view or, for such a simple example, a regular trigger on the table.
This may make the system easier to use than one view/query for inserts
and another proc/query for updates (I'm remain uncomfortable about
referring to an SQL DML UPDATE *command* as a 'query' i.e. it does not
return a resultset).
Which leads me to a question: is it common practice in Access to
differentiate between the 'view' flavour of Query (e.g. prefix of 'vw')
and the 'proc' flavour of Query (e.g. prefix of 'sp') or is it almost
ubiquitous to use a single prefix (e.g. prefix of 'qry') to reflect the
fact that both flavours are lumped together on the same tab in the
Access GUI?
Jamie.
--
onedaywhen
Robert Morley wrote:
> sometimes it's easier to
> construct and maintain complex views by splitting them into several smaller
> ones, even though the smaller ones will never be used outside the context of
> the larger one;
Yeah, I took account of this kind of usage but still thought it a bit
high e.g. using a view/query to do in Access what a CTE does in SQL
Server 2005. It's legitimate of course but you should consider
replacing the 'smaller ones' with derived tables. For example, here's
something I posted recently:
SELECT DT1.Nr, DT1.Name, DT1.Priority,
(
SELECT COUNT(*)
FROM
(
SELECT H1.Nr, H1.Name, H1.Priority,
IIF(H1.Priority = 'Y',
H1.Nr * 0.5,
H1.Nr + 0.00000000000000000001) AS new_rank
FROM Housing AS H1
) AS DT2
WHERE DT2.new_rank <= DT1.new_rank
) AS new_Nr
FROM
(
SELECT H1.Nr, H1.Name, H1.Priority,
IIF(H1.Priority = 'Y',
H1.Nr * 0.5,
H1.Nr + 0.00000000000000000001) AS new_rank
FROM Housing AS H1
) AS DT1;
This looks cleaner using two views/queries:
CREATE VIEW
SmallerOne
AS
SELECT H1.Nr, H1.Name, H1.Priority,
IIF(H1.Priority = 'Y',
H1.Nr * 0.5,
H1.Nr + 0.00000000000000000001) AS new_rank
FROM Housing AS H1;
SELECT S1.Nr, S1.Name, S1.Priority,
(
SELECT COUNT(*)
FROM SmallerOne AS S2
WHERE S2.new_rank <= S1.new_rank
) AS new_Nr
FROM
SmallerOne AS S1;
It could be the case that the two approaches are treated the same by
the parser/optimizer. I'm hopeful that the parser is smart enough to
work out my two derived tables are exactly the same but it could true
that if the derived table must be 'calculated' twice the same will
apply to using the same view twice.
However, code maintenance is more important to me than second-guessing
the optimizer <g>. You've got to decide which approach has fewer
maintenance issues for you e.g. bloating the schema vs repeating code
(which may not be obvious) within the same object. Dervied tables work
for me: I just love the fact that with SQL a complex task can be a 'one
line' solution <vbg>.
Jamie.
--
Robert Morley
> high e.g. using a view/query to do in Access what a CTE does in SQL
> Server 2005. It's legitimate of course but you should consider
> replacing the 'smaller ones' with derived tables. For example, here's
> something I posted recently:
I haven't really familiarized myself with 2005 as yet. I remember reading
something about CTE's, but don't remember exactly what they are. As for the
use of derived tables, yeah, I use those as well when it's something
relatively straight-forward (e.g., "SELECT * FROM SomeTable WHERE
SomeSimpleCondition), but anything with large numbers of conditions, or
joins, etc., GENERALLY gets shunted to a second view to make maintenance
easier. It goes case-by-case, of course, and being human, I don't promise
that what gets evaluated one day for a separate view wouldn't get evaluated
as a derived table six months later. :)
> It could be the case that the two approaches are treated the same by
> the parser/optimizer. I'm hopeful that the parser is smart enough to
> work out my two derived tables are exactly the same but it could true
> that if the derived table must be 'calculated' twice the same will
> apply to using the same view twice.
Yeah, ditto on the "I'm hopeful" part. I can see one possible difference,
which is that the stats that SQL Server maintains may be more efficient with
one method than the other, but nowhere near intimate enough with SQL Server
to comment on whether that's actually the case.
> I just love the fact that with SQL a complex task can be a 'one
> line' solution <vbg>.
Oh yeah, definitely! <vbg back>
Rob
Robert Morley
writing the data layer, or make it friend-only or read-only or whatever it
is I want, but doing it through views would have advantages (as you pointed
out) as well.
As for "common practice", I can't speak to that, as I've seen a wide variety
of methods when it comes to things like action queries, so I'm not sure
what's "common". I sometimes use a prefix for the action type if the action
query is a stand-alone, otherwise I use some grouping name. For example, if
there was a query to delete a test respondent, it might be named
"delTestRespondent". If I had a whole group of action queries that I
normally ran in sequence, I would generally group them based on function, so
maybe something like "TestRespondentDel", "TestRespondentCreate",
"TestRespondentInsertFakeData", etc. (Or I might shorten them all to just
"trDel", "trCreate", etc...depending on how annoying the full spelling got
to be <grin>.)
Because of the fact that an SP in SQL Server can have multiple statements, I
tend not to name based on "action type" when I'm designing for SQL Server.
This has nothing to do with any concept of superiority or what have you,
simply the fact that single-statement SPs are very rare for me in SQL, so
the convention isn't really useful to me there.
Thanks,
Rob
david epsom dot com dot au
> differentiate between the 'view' flavour of Query (e.g. prefix of 'vw')
> and the 'proc' flavour of Query (e.g. prefix of 'sp') or is it almost
:~) Relevant question. As I noted in my long-winded message,
there are conflicting design demands for the access designer.
If you have good eyesight, there is no reason to annotate
'append' and 'update' commands: they are already annotated
by an icon on the 'query' tab.
If you have average eyesight, 'proc' flavours are usefully
annotated as suffixes.
This allows the 'queries' to sort logically by noun or verb.
'Proc' flavours are not visible as source objects in query
design view, so a suffix like 'append' or 'update' may be
used: it doesn't matter how long it is, and it doesn't matter
that names that long are not fully visible in the default
QBE view.
Prefixes are used primarily to locate objects, not to
specify their structure: If I have a table/view/command
prefixed "tblBS", I know that it is on the t tab in the
BS database.
'--------
Complex Access queries are normally built up as a structure
referencing other Access queries: joins are used instead
of subqueries. This makes it very valuable to be able to
easily locate the referenced objects.
I note that on other platforms, it is more common to use
subqueries where an Access designer would reference a
separately stored query.
On other platforms, using a subquery allows you to see
all of the relevant SQL in one place. That is good.
But Access designers have a QBE window which is generally
more productive than SQL view, and in the QBE window
subqueries are poorly displayed, and where possible, Access
queries built using joins between related objects run
better than Access queries built using sub-queries. So
Access developers tend to use stored 'query' objects, and
need to know where to find them.
'-------
(david)
"onedaywhen" <jamiec...@xsmail.com> wrote in message
news:1153295328.4...@i3g2000cwc.googlegroups.com...
onedaywhen
david epsom dot com dot au wrote:
> queries built using joins between related objects run
> better than Access queries built using sub-queries.
I remember reading our very own Albert D. Kallal saying the opposite:
http://discuss.fogcreek.com/joelonsoftware2/default.asp?cmd=show&ixPost=72768&ixReplies=5
"since you are working a query on a query (that is View for you sql
folks!), then often dumping the view, and placing the whole mess into
one query can often help. You can wind up losing some indexing when you
start stacking views on views"
I guess the operative words here are 'often' <g>.
Jamie.
--
David F. Cox
window.
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:eZvPEu5q...@TK2MSFTNGP05.phx.gbl...
david epsom dot com dot au
"David F. Cox" <davi...@ntlworld.com> wrote in message
news:NDKvg.45464$ST2....@newsfe5-win.ntli.net...
> It would be nice if you could right click on a name and get to a
> properties window.
>
! It would be nice if you could drill down into the objects.
Obviating the need to use a naming convention to help you
locate the object in the 'database' window.
But you're right, any kind of properties window would be
an improvement. For a start, there is no way to resolve
aliases other than switching to sql view.
(david)
aaron...@gmail.com
im a more productive sql server developer than your wussy ass sql
server shit eatin mother fucker
using tbl to identify access developers?
access developers-- across the world-- have out-developed sql server
developers for years and years and years.
a productive sql developer writes a database every 6 months
a producitve access developer writes a database in a day
identify access developers-- like it is a bad things
eat shit kid
tbl in a table name is perfectly acceptable.
PERFECTLY.
it's all up to developer preference
onedaywhen
You say that like it is a good thing.
Jamie.
--
aaron...@gmail.com
access developers are just flat-out more productive than any other
developers in the world.
if you idiots would start embracing access; encouraging experienced
developers to grow into adp? all of a sudden you've got strong
developer at bargain prices
i'd just rather have 2 strong adp developers than a single 'data
architect' - gag
and that is what the financial reality is.
ADP rocks.
Access rocks.
Access is the most popular database in the world.
Well, if Access isnt then JET is.
David Cox
need far less pages in my address book. MrSmith, MrsSmith, .....
<aaron...@gmail.com> wrote in message
news:1153015467.1...@i42g2000cwa.googlegroups.com...
> Robert;
>
> don't ask questons; just do it
>
> 'standing on the shoulders of giants'
>
> it is very useful; also when writing classes you start them with a C.
>
> if you have standards like this; it is very very easy to automate
> access if you follow standards like this.
>
>
>
>
> Robert Morley wrote:
>> Because my professional pride hates being insulted by being called an
>> amateur even more than I hate being drawn into pointless debates. :)
>>
>>
>> Rob
>>
>> "BruceM" <bam...@yawwhodawtcalm.not> wrote in message
>> news:uid3uTcp...@TK2MSFTNGP03.phx.gbl...
>> > If the debates are pointless, why are you adding to them? Name things
>> > as
>> > you choose. For myself it would be confusing if account-related
>> > tables,
>> > queries, forms, and reports all start with the same prefix, but that's
>> > just my preference.
>