Re: Replication

"Hugh O'Neill" <hugh.oneill_at_btconnect.com> wrote in message news:3CF41670.A9D005E2_at_btconnect.com... <snip>
> > No, I was referring to the situation of revisions of things like quotes, which is
really a
> > revision 'history'. Say the salesman submits a quote to his client, using say Quote
number
> > 1001, and his client calls back and asks for some of the criteria to be modified,
maybe
> > 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,
and
> > 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
add
> > 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
away
> > 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)
with
> > a 1-1 relationship to the original tables, to store the changes. But I think that
would be
> > a nightmare. Having never handled this situation of revising data before, I was
wondering
> > 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
table
> separate from that containing the quotation number and put them in a many-to-one
relationship
> (one quote number - many versions). This may well consist mainly of pointers to data in
other
> tables (customer, products, etc.).

I don't see how that addresses the issue of avoiding the duplication in all the child tables.
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.

<snip>

> The 'temporary' idea might be a runner, particularly if you can establish that
quotations only
> go out after Approval at Head Office. However, the Salesman Ref would be a cleaner
solution
> and, once established as a company procedure, will be a significant part of the
quotation
> identity. Remember that multi-departmental organisations often send out invoices
identified in
> 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.

<snip>

Graham Received on Wed May 29 2002 - 20:14:54 CEST