At 12:58 PM 15/03/2006, you wrote:

>On Wed, 2006-03-15 at 00:06 +1100, Helen Borrie wrote:
> > No accounting standard is allowed to override
> > best practice in database design.
>
>Explain that to the former bosses at Enron :o)
>
>Accounting standards, rather than overriding best practice in database
>design, define best practice in database design for financially critical
>systems.

No way. No way. Best practice *includes* protecting the integrity
of externally imposed data requirements and *not* making internal
data integrity dependent on external requirements, actual or potential.

A primary key is an entirely internal matter. The accounting
standards are met entirely by associating the unique protected
document id (such as invoice id) with the primary key. Database
design best practice is NOT met by making the document id perform the
function of the primary key.

There's nothing to stop you putting a UNIQUE constraint on the
protected field: it's what you must do to comply with the accounting
standard. Furthermore, you can set up all the protections stipulated
by the accounting standard by applying constraints and triggers. You
don't spin protected document ids out of calculations based on
pre-existing documents. You create an engine for it that is used for
no other purpose and never allows an update. Down the chain of
operating processes, the protected document id is literally read-only
and is never seen again in the abstract storage. Foreign keys and
everything else in the system refer to this totally-protected thing -
"invoice" or whatever, only by way of the PK of the master
entity. End-users never see the technology - they never lay an eye
or a finger on a structural object like a primary or foreign key.

That's the science - the engine behind what you present to the
customer to meet the requirements. The accountant has no more place
telling you how to build the engine that he does telling the
equipment-maker how to wire the adding machine.

./heLen