Date: Thu, 30 May 2002 04:14:54 +1000
"Hugh O'Neill" <hugh.oneill_at_btconnect.com> wrote in message
> > No, I was referring to the situation of revisions of things like quotes, which is
> > revision 'history'. Say the salesman submits a quote to his client, using say Quote
> > 1001, and his client calls back and asks for some of the criteria to be modified,
> > for a cheaper product to be used. One option could be to go back and change the stored
> > data, but then there is no way the salesman could later see the price he originally
> > quoted. Another option is that the salesman must create a new quote number, say 1007,
> > write on quote 1001 a reference like "see revision quote #1007". But a nicer way of
> > keeping track of which revision is which, is to keep the same quote number, 1001, and
> > a revision number, 2, to make it 1001-2, or with the salesman's initials, BJ1001-2.
> > Anyway, what this means is that the majority of fields would be duplicated (getting
> > from a normalised db), which is probably an idiosyncracy of storing histories. Maybe
> > another way (thinking on the fly here) is to create identical tables (maybe split up)
> > a 1-1 relationship to the original tables, to store the changes. But I think that
> > a nightmare. Having never handled this situation of revising data before, I was
> > if there was some general way this is done.
> > Hope I explained this ok.
> Well that simply means that you have your table of quotations details versions in a
> separate from that containing the quotation number and put them in a many-to-one
> (one quote number - many versions). This may well consist mainly of pointers to data in
> tables (customer, products, etc.).
I don't see how that addresses the issue of avoiding the duplication in all the child
Agreed, where the data points to data in other tables, there is no real problem. But when the data itself is on the 1 side of a 1-many, then the duplications start multiplying exponentially.
Eg. 1 quotation (tblQuotes) contains many areas (tblSections) (say 10) of a building in
which the floor is to be treated.
Each floor treatment has many ingredients (tblIngredients) (say 5). Making a revision to change some ingredients in a treatment, or the addition of a contract clause (there's no telling which part of the quote needs to be changed), means a duplication of something like 50 records - each revision; there could be 10 revisions before a job commences.
I'm now thinking about the possibility (haven't tried it yet) of splitting each table up into smaller fragments, with a 1-1 join. Then any duplications would be localised to its own fragment.... ooh, still not sure about this one.
> The 'temporary' idea might be a runner, particularly if you can establish that
> go out after Approval at Head Office. However, the Salesman Ref would be a cleaner
> and, once established as a company procedure, will be a significant part of the
> identity. Remember that multi-departmental organisations often send out invoices
> similar ways to this; there's nothing new or insurmountable in this.
Well, fair enough. I'll run it by them and see how they feel. They're a small, new-ish company, and don't have a company-wide consistent quotation numbering system yet - each salesman has his own system.
Graham Received on Wed May 29 2002 - 20:14:54 CEST