Re: Replication

Please see in line below:

hth

Hugh

Graham Bellamy wrote:

> "Hugh O'Neill" <hugh.oneill_at_btconnect.com> wrote in message
> news:3CF2C265.6576374_at_btconnect.com...
>
> > > Thanks Hugh, I'll give it some consideration. My first thoughts are that I don't like
> the
> > > duplication of the number part of the Quote number; I was hoping to keep them unique
> from
> > > 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
> guess
> > > 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
> the
> > numeric level. You could consider issuing blocks of numbers for each relevant user but
> that's
> > going to be very messy, hard to maintain and actually achieve nothing that a simple
> salesman
> > identifier doesn't do anyway.
> >
> > >
> > > Is it a standard practice to copy an entire record (with all it's child relations),
> even
> > > if the revision is to adjust one field (could be any field)? I can't think of any
> other
> > > 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
> the
> > 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
> individual
> > 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.
>

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.).

>
> 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?
>

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.

>
> 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.)

I still think the Salesman ID is the way to go on this. It's just as valid in-house as on the road.

>
>
> Thanks again,
> Graham
Received on Wed May 29 2002 - 01:44:49 CEST