Re: Replication

From: Michael Morrison <mcmorris_at_us.ibm.com>
Date: Fri, 07 Jun 2002 17:36:07 -0700
Message-ID: <3D015177.29C16CCC@us.ibm.com>

Another possibility: don't use the quote number as a key. Assign some other value as the primary key that none of the sales staff (or whoever) use for their reference -- it should be used by the DBMS only. Then your applications look for all rows with the quote number (build appropriate nonunique indexes for this column). All the history is in the base table and is retrievable (and replicatable or archivable by quote number). You will get duplication of rows in other tables with FK relationships to rows of a particular quote, and your applications will have to create the new rows in the dependent tables for each update to the quote in the parent table, but your data will be much easier to replicate in this case. To ensure that you always update/view the most current quote, add a timestamp column for the SELECT ... Michael

Graham Bellamy wrote:

> "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 Fri Jun 07 2002 - 19:36:07 CDT