I'm trying to put together a table of transactions which are applied to
accounts, the account holders are listed in another table.

I'd like to have an auto-increment field to sequence the transactions.
That way I can both select on account number to get all the transactions
on an account, sort on account number and transaction to get all the
transactions in the right order and do a "strawberry query" to find the
most recent transaction for every account.

The only complication is that some transactions are amendments to previous
ones, either changing a transaction or voiding it completely. In this case
I want to write another record, but leave the original one in place, so
that I have an audit trail of changes.

What I was thinking of doing was having two fields. The transaction number
autoincrements as new records are added, but there is also a "refers to"
field which, if the record is an amendment, contains the transaction
number of the transaction being amended. The original transaction has the
same number in both fields.

Now I can select original transactions on the criterion that
transaction=refers, and do a strawberry query to find the last amended
form of any transaction. This, plus a filter to remove voids, produces my
customer statements.

The only problem is this: How do I fill in the "refers to" field on the
original transaction?

Subsequently it's quite easy as I have to do a read-modify-right to do an
amendment or a void. It's the first record, where I need to set "refers
to" to be the same as the value generated by the autoincrement, that
worries me.

I could insert the record with "refers to" zero, then immediately use
LAST_INSERT_ID() to amend it, but I feel sure there ought to be a way of
doing this in one query.

On Sat, 30 Oct 2010 12:18:51 GMT, Robert Billing wrote:
You may wish to simply leave this null instead.

Are there business rules that require this to refer the record to
itself?

Essentially, so far, all you've really implies is that you want to know
when you've got an ultimate parent record instead of a child record of
something else, and are planning to compare that value to the record's
ID to see if that's the case. Seeing whether the refers-to column is
null doesn't take any more effort. And making something null on insert
doesn't require you to know any value, so it doesn't have the complication
that you're running into.

--
32. I will not fly into a rage and kill a messenger who brings me bad
news just to illustrate how evil I really am. Good messengers are
hard to come by.
--Peter Anspach's list of things to do as an Evil Overlord

The hyperspace communicator crackled into life and we heard Peter H.
Coffin say:

The way I saw it was that it would be useful to be able to do a GROUP BY
on the refers field to get the parent record and all its children.

Similarly sorting on the refers field and then the sequence would produce
the original record, followed by the changes. This is something I will
need to generate as a report.

On Sat, 30 Oct 2010 16:53:32 GMT, Robert Billing wrote:
Hmm... Interesting thought...

Okay, the nice thing is that both the null and the refers-to being the
same *mean* the same thing. So it doesn't matter much which it is, and
so long as there's no other condition that would make this happen, you
can flip between them at need. For example, the records can start out as
null-parent, then you can

UPDATE my_table
SET p_id = id
WHERE p_id IS NULL

and do the reports, then

UPDATE mytable
SET p_id = NULL
WHERE p_id = id

to put it back if you want. This is a little messy but it might make up
for it in the sense that while you're running the reports, it can still
be active and doing it's thing but you've got the state of it saved AS
OF WHEN YOU STARTED THE REPORTING. That is, any new records will show up
null-parent instead of self-parent, regardless of how long you're
fussing around with the reporting, so long as you only count stuff that
has a not-null parent.

--
It is odd, but on the infrequent occasions when I have been called upon
in a formal place to play the bongo drums, the introducer never seems
to find it necessary to mention that I also do theoretical physics.
--Feynman

On 2010-10-30 14:18, Robert Billing wrote:
[...]
One way is to split this relation in two, transaction and
transaction_refers_to. An original transaction has no row in
transaction_refers_to.

[...]

/Lennart

The hyperspace communicator crackled into life and we heard Peter H.
Coffin say:


Actually, since I'll never need to put it back, the first of your code
samples will do exactly what I want. Essentially the system may run for up
to a week with transactions going in, then somebody decides it's time to
run reports, print confirmation letters and whatnot, so at that point we
can do a quick tidy up on all the refers_to fields. Any transactions that
come in while the report is running will be cut off on the timestamp
anyway, as the report will be "transactions between <date/time> and <other
date/time>".

Thanks.

In article <g4Yyo.43915$gN7.33835 (AT) newsfe12 (DOT) ams2>,
Robert Billing <unclebob (AT) tnglwood (DOT) demon.co.uk> wrote:
Just do a GROUP BY or ORDER BY based on COALESCE(refer,transaction). That
expression will return refer if not null, and transaction otherwise.

Cheers
Tony
--
Tony Mountifield
Work: tony (AT) softins (DOT) co.uk - http://www.softins.co.uk
Play: tony (AT) mountifield (DOT) org - http://tony.mountifield.org

The hyperspace communicator crackled into life and we heard Tony
Mountifield say:

Thanks. I felt there had to be an easy way I hadn't thought of.