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

[Robert Morley]

The DBAs should be intimate enough with the

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]

But the apartment number will be very helpful.

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]

Keep in mind that views don't ONLY represent entities (in fact, I almost
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

 

[Robert Morley]

Alright, that's fair enough. I generally just ignore the metadata when I'm
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

 

[Robert Morley]

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:

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

 

[david epsom dot com dot au]

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

:~) 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)

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.

 

[David F. Cox]

It would be nice if you could right click on a name and get to a properties
window.

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

:~) 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)

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.

 

[david epsom dot com dot au]

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

it is a good thing

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]

I have discovered that if I precede names with descriptor abreviations I
need far less pages in my address book. MrSmith, MrsSmith, .....