[thesite] last 5 query

[jeff]

dean,

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: Dean Mah
:
: Using a trigger means implementing more than trivial
: business logic.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

what would you call a trigger that performs a cascading delete to maintain
data integrity in child tables?  is this just trivial business logic or does
it do the job (of maintaining relational integrity) at the closest point to
the data?  does it make sense to have the programmer have to remember that
he must delete child records whenever he performs a delete on the parent
table (which could conceivably happen in more than one place in the
application)?

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Primary and foreign keys, and to a lesser extent,
: check constraints implement trivial data robustness
: checking.  That said, I would never rely on database
: constraints.  I'll always try to catch data inconsistencies
: within the application.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

i can agree with constraints.  once the application is built, the
constraints don't really play much of a role as the application code must be
written to shield the user from the constraint errors anyway.

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Containing some of the business logic in the database
: usually makes it more difficult for them to find bugs and
: figure out the system.  For example, it is usually more
: difficult for them to track down how a table is being changed
: when it is triggered by an update to another table.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

where do you draw the line then?  no triggers that affect the data on insert
or update?  what about triggers that delete dependent data when parent data
is deleted?  if you say that's a foreign key then you're wrong.  a true
foreign key can only restrict deletes.  it can't perform a cascading delete.

i still say the answer is to use the tools as close to the data as possible.
if the programmer doesn't understand what's happening with the data then you
have at least two problems on your hands.  first, they're in over their
head - too little knowledge or experience for what they've been asked to do.
second, whoever is directing the project is doing a poor job of
communicating the details of the project via database schemas, requirements
documents, etc.

now, if you're doing complex architecture changes with triggers like writing
temp tables, adding/removing columns, changing datatypes, etc. then the
problem isn't with triggers.  the problem is with the approach to the
problems.  the solutions are causing more problems than they're solving.

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: All of the industry (information technology in general) people
: that I've talked to, they've never had good things to say about
: the maintainability of triggers in a complex systems.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

what exactly are you saying?  how are they difficult to maintain?  in my
experience, i dare say that you can't efficiently build a solid database or
application architecture without utilizing triggers in some form.  if the
database solution you're using is difficult to maintain and isn't giving you
the flexibility and performance to offset that then it's time to find
another database solution.

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Whether the additional code sits in the application layer or
: the database layer there is still the same potential for coding
: errors.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

don't get me wrong.  i completely agree with that.  i'm just saying that the
fewer lines of code you have to write, the less likely you are to make an
error.  again, in my experience it has always been easier to write code at
the database level to perform the necessary tasks.

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: And I wouldn't agree that the application code would have
: more convoluted logic than the database code.  If anything,
: you need to know a couple of languages which makes the
: maintenance effort greater and requires a higher knowlegde
: level on the part of the developers.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

i think it'd be fair to say that if you're a programmer that's working with
building dynamic web applications you better be solid with more than just
one or two languages.  you better know the middleware language (asp, cf,
php, etc.), you better know html, and you better know sql really well.
nothing earth-shaking there.  that's just the facts of building a quality
solution.  if you're not solid with these (and probably javascript as well)
then you probably don't have any business being employed in that capacity.

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > i agree.  however, i think there's an even better approach to
: > this issue.  rather than having a "last 5" table, why not just
: > have a "last 5" file that's altered whenever there's an insert
: > into the database.
:
: I'd still like to see this implemented at the application level.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

now we're saying the same thing.  i think the performance hit should occur
when a new record is inserted into the database - not whenever a visitor to
the site requests a page.

thanks,

.jeff

name://jeff.howden
game://web.development
http://www.evolt.org/
mailto:jeff at members.evolt.org