Pointless debates on the finer points of naming your objects (moved from Combo Box Requery thread)

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.

--

Absolutely no question that this lead to problems, which (as I said) is why
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

"onedaywhen" wrote in message
oups.com...

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.

--

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.

--

"onedaywhen" wrote in message
ups.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 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).

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.

--

"I now see it is a potential be a hindrance"

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" wrote in message
ups.com...

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 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.

--

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?

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" wrote in message
ups.com...

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 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.

--