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

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

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

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

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

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

--

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.

--

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

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

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

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.

--

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

"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
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 structu 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" wrote in message
ups.com...

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.

--

david epsom dot com dot au wrote:
Access
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/joelonso...68&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.

--