| Subject | Re: [firebird-support] Auditable Invoice Number |
|---|---|
| Author | Helen Borrie |
| Post date | 2006-11-28T12:31:30Z |
At 07:39 PM 28/11/2006, you wrote:
number (and its strict auditability) is a crucial piece of data in
the user world then it should *not* be the primary key of the Invoice
table. It should be a uniquely constrained column that has its own
protections (see the suggestions in the Auditable Series article).
Furthermore, that old Paradox-style hierarchical foreign key is a
design flaw that should not be propagated into a database system that
supports true foreign key constraints. The invoice number does not
have to appear in the detail record structure at all. A foreign key
to the (surrogate) primary key of the invoice table will find the
associated invoice, whatever it might eventually be.
Depending on the business rules, it may be safest to defer applying
the actual invoice number until the invoice is confirmed. In
practice, this can often happen when the invoice is printed - a
still-present hangover from the days of pre-numbered stationery. Any
of the distribution systems I've ever been involved with raise an
order first. Companies often require a credit check before
authorising an invoice. Until the order is confirmed, the invoice
doesn't legally exist.
./heLen
>CM,This approach is a serious mistake in design terms. If the invoice
>
>If you wish to use a generator to provide a contiguous number series
>for invoices here are some thoughts
>
>The invoice will require two tables (a) the invoice header table and
>(b) the invoice line items table.
>
>The unique id for the header table will be the invoice number. The
>unique id for the line items table will
>be a combination of invoice number and sequence/line number of the
>invoice item
>
>At the time of storing the line item data you will need to know the
>invoice number.
>
>So,
>
>Step 1, use a stored procedure to insert the invoice header record
>and return the invoice number to the
>application.
>
>Step 2 , you then use the returned invoice number when storing line
>item transactions.
>
>If in the same tables you will also store credit note transactions
>then just use the same generator and method.
>
> From this it is simple to generate reports such as sales journals etc ..
number (and its strict auditability) is a crucial piece of data in
the user world then it should *not* be the primary key of the Invoice
table. It should be a uniquely constrained column that has its own
protections (see the suggestions in the Auditable Series article).
Furthermore, that old Paradox-style hierarchical foreign key is a
design flaw that should not be propagated into a database system that
supports true foreign key constraints. The invoice number does not
have to appear in the detail record structure at all. A foreign key
to the (surrogate) primary key of the invoice table will find the
associated invoice, whatever it might eventually be.
Depending on the business rules, it may be safest to defer applying
the actual invoice number until the invoice is confirmed. In
practice, this can often happen when the invoice is printed - a
still-present hangover from the days of pre-numbered stationery. Any
of the distribution systems I've ever been involved with raise an
order first. Companies often require a credit check before
authorising an invoice. Until the order is confirmed, the invoice
doesn't legally exist.
./heLen