Re: Replication

From: Graham Bellamy <>
Date: Wed, 29 May 2002 02:11:34 +1000
Message-ID: <ad0a37$gt$>

"Hugh O'Neill" <> wrote in message

> > Thanks Hugh, I'll give it some consideration. My first thoughts are that I don't like
> > duplication of the number part of the Quote number; I was hoping to keep them unique
> > quote to quote. However, I am now thinking of the situation of revisions (slight
> > modifications) of quotes, in which it would be visually more logical to the user to
> > duplicate the number part, and add a revision field. So if I'm going to do that, I
> > adding the salesman's initials to the number is not much different.
> >
> Since the systems are seldom connected, there's no real way of avoiding duplication at
> numeric level. You could consider issuing blocks of numbers for each relevant user but
> going to be very messy, hard to maintain and actually achieve nothing that a simple
> identifier doesn't do anyway.
> >
> > Is it a standard practice to copy an entire record (with all it's child relations),
> > if the revision is to adjust one field (could be any field)? I can't think of any
> > practical way of doing it. Surely this blows Normalization to pieces.
> I don't understand what you mean here. What copying are you referring to? If you mean
> replication, what that does is to synchronize the temporarily connected systems so their
data is
> made the same. When the systems are separated again, each remains normalised as an
> database system.
> hth
> Hugh

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.

Regarding Replication, I had thought of issuing blocks of quote numbers to last a number of months, but like you, thought this would be too messy. However the suggestion of adding the salesman's initials means that there exists the possibility of there being a BJ1001 (Bill Johnson) that was created on the replicated db, and a JS1001 (John Smith) on the main db (in the office). I'm not sure I like this idea, as this may create confusion with revisions. Maybe I might have to make a 'temporary' Quote number, and tell the salesman that he can create the quotes while not connected, but can't print out and send them until he updates. Then I would have to write code to change the temp quote numbers to unique, and useable ones. What do you think?

Actually, I don't think the systems would be 'seldom connected'. The question was put to me as a "what if...". I think it would be more that the db would be mostly used on the network, and 'occasionally' the salesman might want to take his laptop home with him. This is a progressive project, where I develop it to a certain point, and can add more functionality later (I know, I know - bad way of doing things, but I'm not a professional, and have no idea as to how long it will take me to create an entire package. I'm treating this as a learning experience.)

Thanks again,
Graham Received on Tue May 28 2002 - 18:11:34 CEST

Original text of this message