Re: trigger advice needed

> Here's the scenario with all the gory details:
> MASTR_FILE is a file of parking tickets.  The agency responsible for 
> "parking enforcement" is embarking on a campaign where parking
> ticket scofflaws would receive Default Judgments for unpaid parking
> tickets.  Before we can file a Default Judgment, we must send the
> individual a Default Judgment Notice.  To "qualify" to receive a 
> Default Judgment Notice, an individual  would have to have atleast 
> 5 unpaid parking tickets.  Once a Default Judgment Notice is sent,
> an individual has 30 days to pay the tickets or a Default Judgment 
> is filed in the county clerk's office.  My problem occurs when someone 
> with 5 unpaid tickets receives a Notice, and proceeds to pay off
> 1 of the parking tickets.  When a tickets paid, it's status changes from
>a 'N' to a '0'.
>
>    This individual would then have 4 unpaid tickets, so they would
> not be eligible to receive a Default Judgment.  I then need to change
> the status  of the other 4 tickets from 'N' to whatever it was before the
> Default Judgment Notice was sent.>

David

As I suspected, Data base design.

If you had a Master File for the "Person", and Transactions for the tickets,
it would be a better design.   You are looking at a "Cumulative" status
based on multiple transaction States with the Default Notice thing.  If the 
True master file for that person(As opposed to the ticket transactions)
had a flag to indicate that person's "Default Notice" status,  leaving 
each ticket transaction either paid, not paid, etc.  You could reset that
one person's Master File flag upon receiving payment for the one 
Ticket Transaction.  Each ticket transaction would then once again 
become a "Single Fact Holder" in stead of "Multiple Fact Holder" that it
is now(It's own status and the  "Cumultative Status" it participates in)

I know you probably do not have latitude to change the file design,  But
I want to clearify the fact that in my years of IS,MIS,IT that the majority
of the time if we have to do gymnastics in a program, it's usually because
of the underlying data base design.  


You can get indications that the design is wrong if you have to "Remember"
what the previous statuses were and reset each transaction back to that
value(""to whatever it was before the Default Judgment Notice was sent"") 
when a "Cumulative" event happens to the real master file(the person entity).

The SQL example that was sent seemed neat.  
BUT,  Don't mistake the fact that,  even though there is a "Trick" way 
of getting out of the mess, that the reason for the "mess" in the first
place, as nearly always, it data base design.  (Roll over Codd).

Just a thought

John Carr
EdgeTech
Have Classes,  Will Travel


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---