And the invoice line number is also a PITA to keep up to date if you allow
deletions, insertions, etc. I agree with Arvin here. Autonumber primary key,
Invoice Number fk, and some other mechanism for ordering line item details
where required. A date/time stamp for each detail entry can be used for
retaining the original entry order.
Arvin Meyer wrote in message <7joig1$342$1_at_esinet2.esinet.net>...
>If you look at the needs of the data structure, rather than an example of
>the output required for a single invoice, you'll see that only the Foreign
>Key from the many side, participates in the join. If you only include a
>single field of a multiple field index, you cannot optimize the query.
>
>The order you required can be maintained either with an autonumber PK or
>with an indexed linenumber, however the database will enforce the
autonumber
>PK index at the table level without any additional code. The linenumber may
>be required for display, but not for storage. In an invoice (report) it can
>be computed and sorted using a running sum.
>---
>Arvin Meyer
>onsite_at_esinet.net
>
>Vincent Quesnoit wrote in message
><375F62F7.E7EBB45D_at_bull.net.Earth.Solar_System.milky_way>...
>>
>>I found some instances when that kind of field (invoicelinenumber) is
>required
>>as a complement to the detail primary key, that is when you need to record
>some
>>order amongst detail records. I have a non-substantiated feeling that the
>>autonumber primary key may not be the right field to do that.
>>Once you do need that second level key, the Detail primary key looks
>redundant.
>>
>>This design problem occured recently when I tried to break expressions
such
>as :
>>(a or b) and((b or c) and z) into a sequence of records to get a tabular
>>representation. There you obviously need a key to designate an expression,
>and a
>>key to order the elements within the expression.
>>The big concern is : how do I let people change the content of an
>expression,
>>and keep it in order. For me the answer was a LineNumber field.
>>In that context the Autonumber key is redundant.
>>
>>Vincent Quesnoit
>
>
Received on Fri Jun 11 1999 - 08:41:04 CDT
