Re: "code" tables?


"Ed Prochak" <edprochak@xxxxxxxxx> wrote in message
news:4b1507c9-207e-4555-bd27-42dd0e84de9d@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jun 26, 12:31 pm, "David Cressey" <cresse...@xxxxxxxxxxx> wrote:
"Roy Hann" <specia...@xxxxxxxxxxxxxxxxxxxxx> wrote in message

news:T_adnWNsCqvQDP7V4p2dnAA@xxxxxxxxxxxx



"Arved Sandstrom" <asandst...@xxxxxxxxxxxxx> wrote in message
news:VEL8k.493$7%6.472@xxxxxxxxxxx
Not "getting" data is part of a larger problem, which is forgetting
what
software is actually for. Most end users couldn't care less about
how
their solution is implemented, they just want a useable program.

End-users absolutely do not care about anything except how easily they
can
get through the 9-5. They don't own the business and they don't pay
it's
bills and they have no investment in it's most valuable (and
expensive)
non-tangible asset--namely it's data. The end users' opinions on the
subject of databases is as relevant as the cows' opinions on
cheese-making.

Useable meaning reliable, not too hard to use, reasonably fast and
so
forth. Not "getting" data is also accompanied by not "getting" user
interfaces, reporting requirements, documentation, error recovery
etc.

Not "getting" data means being a fraud and an imposter. No business
buys
database applications just to run applications; they buy them to get
accurate information and for no other reason. Not "getting" the other
things you list means delay and awkwardness and you'll surely be in
trouble
if you can't deliver efficiency and speed, but those come second. To
prove
it, propose to a company director that you can make his slow systems
twice
as fast but they'll produce undetectable corruptions of the data, and
see
if
he goes for it.

I can't tell you the number of times programmers have come to me for
help
with a query
that produces wrong results, and when I try to change their "select" to
"select distinct" to see if maybe that's the problem, they exclaim "Oh
I
never use 'select distinct'! It runs too slow!"
I patiently explained to them that I first want to come up with a query
that
is logically correct, then if necessary, come up with one that's
logically
equivalent, but runs fast.

When I see
SELECT DISTINCT
in anything but an ad hoc query, I know something is wrong with the
query. Possibly it is joining to a wrong table (to a detail table
instead of a header/parent table for example) or some filtering
condition is missed (a column not in the select list needs to be
used), or some join condition was missed (there is a compound key and
one component was left out). Even in an ad hoc query I tend to use
SELECT COUNT(*)
since that gives me a little bit more information for the same cost as
DISTINCT (i.e. it reads the same amount of data).

Seeing this post, I'm actually a little disappointed in you, David.
Your last sentence does show you mainly follow a good approach. It's
just that DISTINCT should be left to ad hoc queries IMO.

Have a good day.
Ed

Your comment doesn't agree with my experience. In general, there are two
circumstances where SELECT DISTINCT is the right approach. Both involve
situations where SELECT generates a bag, but the desired result is a set.

The first is a situation where the database has been misdesigned. If the
database has been frozen for a year or more, and there is a lot of
production software that depends on the existing table design, it may
simply be unfeasable to correct the design in order to make one query
logically easy to write.

The second is a situation where the query requires a subset of the data that
does not include any candidate keys from the underlying tables. In that
case, the generation of a query requires its own mechanism for eliminating
duplicates.


Perhaps the development of a new program, a long time after the database is
in production, and not easily altered, has much the same flavor of what you
have called an "ad hoc query". Using the database to organize data in a way
that the designer did not contemplate.



.

Relevant Pages

  • RE: Creating a query with dynamic fields
    ... As for the table design, you're probably right, but I'm working on this ... database for a colleague, and I believe that the table needs to be in this ... filter records such that only those records with a value greater than a ... query can vary depending on the value of a control on a form? ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Key Violations in Append Queries
    ... I even added a test field and that does not append. ... returns with the other 3 records and cant be removed from the query results. ... the design of the database requires uniqueness for the key involved. ... Whoever designed the database supposedly felt this was necessary. ...
    (microsoft.public.access.queries)
  • Re: OT: SQL
    ... query processing. ... FROM Employees e, Employees m, Management mgt ... Manager and Employee Salaries. ... The scheme used does not model database files in general, ...
    (sci.logic)
  • Re: Query Design View is Slow to Open
    ... My benchmark query takes about 20 minutes to open in design view. ... Each subsequent time, the query opens ... In the same database, there are many queries that open normally in ...
    (comp.databases.ms-access)