Re: [NOVICE] Recursive relationship - preventing cross-index entries.

Michael Glaesemann wrote:
> INSERT INTO accounts_receivable_receipts (accounts_receivable_id,
> receipt_id)
> VALUES (2, 1), (4, 3), (6, 1), (5, 3);
>
> I have not done much accounting-style design, and I don't think this is
> really the best way to set these up (for example, I think it's a bit odd
> to map these amounts against each other without indicating how much of
> the amount is matched), but without more information about your business
> logic, I don't really know what else to suggest.

In most I've seen, you have an "allocations" table linking payments
received against individual invoices. So, you might have payment 1234
with £100 against inv #10001 and £150 against inv #10002.

The allocations table tends to have an "amount unallocated" column too,
imposing an order to the allocations. Not strictly necessary from a
database point of view, but I suspect left over from manual methods of
working.

--
Richard Huxton
Archonet Ltd