Hello,

I need a bit of advice for the following problem as I do not know where to
start.

My client has a sales prospection database in the office. each salesperson
has access to it's own clients and prospects inside the company wide
database. The sales director has access to all the prospects and clients. So
far it is fairly simple.

Now the sales manager wants each sales each sales person to have a netbook
with a copy of the /subset/ of the client database he deals with, (in total
around 20 tables) along with his agenda etc ... during the week, whilst
travelling from client sites to client sites, the sales person can write
reports about his visits (these are indexed in the database), amend the
client/prospect database (add contacts, amend contacts, add notes ....)
upon his return to the head office, the sales person 'plugs' his netbook to
the network, press the 'synch' button and pooof all by magic all changes
made locally are merged with the central database, taking into account any
changes made to the central database. Once this merge process is finished,
the sales person netbook is refreshed with a copy of the new database
fragment he deals with.

Note that there are some update rules (like who owns the propect/client
record in case of update conflicts). All primary keys for all records are
numerical ids, handled by sequences.

Instead of reinventing the wheel I thought I would ask what you all reckon
would be the best strategy

- to merge the changes (updates and inserts) back to the central database,
baring in mind that the central database may have also be updated and so
there may be primary key conflicts which needs to be dealt with
automatically.
- to extract the portion of the database the sales person needs to get. (he
cannot have the full database at any one time for security reasons and since
he uses a notebook, the speed is good but not great, so I can't really
restore a full dump and then delete what the sales person does not need )

- all of this, of course in a reasonably short amount of time.

I am open to any suggestion, my goal being to have something simple, and
reliable.

I would like thank in advance any of you that will go through the trouble of
answering my questions

Didier

Search Discussions

  • Jens Wilke at Jan 8, 2011 at 11:25 am
    On Samstag, 8. Januar 2011, Didier Gasser-Morlay wrote:

    Hi,
    Instead of reinventing the wheel I thought I would ask what you
    all reckon would be the best strategy
    May be git is an easier to use tool for your demands.

    Rgds, Jens
  • Mladen Gogala at Jan 8, 2011 at 6:22 pm

    On 1/8/2011 1:26 AM, Didier Gasser-Morlay wrote:
    Now the sales manager wants each sales each sales person to have a
    netbook with a copy of the /subset/ of the client database he deals
    with, (in total around 20 tables) along with his agenda etc ... during
    the week, whilst travelling from client sites to client sites, the
    sales person can write reports about his visits (these are indexed in
    the database), amend the client/prospect database (add contacts, amend
    contacts, add notes ....) upon his return to the head office, the
    sales person 'plugs' his netbook to the network, press the 'synch'
    button and pooof all by magic all changes made locally are merged with
    the central database, taking into account any changes made to the
    central database. Once this merge process is finished, the sales
    person netbook is refreshed with a copy of the new database fragment
    he deals with.
    Your sales manager should get acquainted with something called "the
    Internet". The sales people should only log into the website providing
    service, instead of carrying the database around. Also, they shouldn't
    be carrying "notepads", "laptops" or any such contraption, they should
    be carrying iPads. Make sure to spell it right, "iPad" is not the same
    as "iPod". Your sales manager has a lot to learn.

    --
    Mladen Gogala
    Sr. Oracle DBA
    1500 Broadway
    New York, NY 10036
    (212) 329-5251
    www.vmsinfo.com
  • Didier Gasser-Morlay at Jan 8, 2011 at 7:36 pm
    Although I would tend to agree in theory with you, (and it was my first
    reaction), I would like to point out that I can say 'Won't work' quicker
    than you can spell Ipad or even :) amongst the reasons is the fact that they
    are not connected all the time in the area where they operate internet
    access is still far from being guaranteed at a decent speed. And the last
    thing I want to hear from this client is 'they are not using it because it
    is too slow'

    So thank you for the bit of advice but no thanks
    On 8 January 2011 19:22, Mladen Gogala wrote:

    On 1/8/2011 1:26 AM, Didier Gasser-Morlay wrote:

    Now the sales manager wants each sales each sales person to have a netbook
    with a copy of the /subset/ of the client database he deals with, (in total
    around 20 tables) along with his agenda etc ... during the week, whilst
    travelling from client sites to client sites, the sales person can write
    reports about his visits (these are indexed in the database), amend the
    client/prospect database (add contacts, amend contacts, add notes ....)
    upon his return to the head office, the sales person 'plugs' his netbook to
    the network, press the 'synch' button and pooof all by magic all changes
    made locally are merged with the central database, taking into account any
    changes made to the central database. Once this merge process is finished,
    the sales person netbook is refreshed with a copy of the new database
    fragment he deals with.


    Your sales manager should get acquainted with something called "the
    Internet". The sales people should only log into the website providing
    service, instead of carrying the database around. Also, they shouldn't be
    carrying "notepads", "laptops" or any such contraption, they should be
    carrying iPads. Make sure to spell it right, "iPad" is not the same as
    "iPod". Your sales manager has a lot to learn.

    --
    Mladen Gogala
    Sr. Oracle DBA
    1500 Broadway
    New York, NY 10036
    (212) 329-5251www.vmsinfo.com
  • Lew at Jan 9, 2011 at 4:25 pm
    (Please don't top-post.)

    Didier Gasser-Morlay wrote:
    Although I would tend to agree in theory with you, (and it was my first
    reaction), I would like to point out that I can say 'Won't work' quicker than
    you can spell Ipad or even :) amongst the reasons is the fact that they are
    not connected all the time in the area where they operate internet access is
    still far from being guaranteed at a decent speed. And the last thing I want
    to hear from this client is 'they are not using it because it is too slow'

    So thank you for the bit of advice but no thanks
    Wrong results in half the time!

    The problem with your contemplated approach is that you potentially sacrifice
    correct information for speed. It'll take a *lot* more time and expense to
    fix consequences of bad information than to wait ten extra minutes for good
    information.

    --
    Lew
    Ceci n'est pas une pipe.
  • Jayadevan M at Jan 10, 2011 at 4:54 am
    Hello,
    Although I would tend to agree in theory with you, (and it was my
    first reaction), I would like to point out that I can say 'Won't
    work' quicker than you can spell Ipad or even :) amongst the reasons
    is the fact that they are not connected all the time in the area
    where they operate internet access is still far from being
    guaranteed at a decent speed. And the last thing I want to hear from
    this client is 'they are not using it because it is too slow'

    So thank you for the bit of advice but no thanks
    If you have enough time for research and development, please have a look
    at
    http://gears.google.com/

    One of our products faced a similar situation and used it with
    considerable success.

    Regards,
    Jayadevan






    DISCLAIMER:

    "The information in this e-mail and any attachment is intended only for
    the person to whom it is addressed and may contain confidential and/or
    privileged material. If you have received this e-mail in error, kindly
    contact the sender and destroy all copies of the original communication.
    IBS makes no warranty, express or implied, nor guarantees the accuracy,
    adequacy or completeness of the information contained in this email or any
    attachment and is not liable for any errors, defects, omissions, viruses
    or for resultant loss or damage, if any, direct or indirect."
  • Lew at Jan 9, 2011 at 4:25 pm

    Didier Gasser-Morlay wrote:
    Now the sales manager wants each sales each sales person to have a netbook
    with a copy of the /subset/ of the client database he deals with, (in total
    around 20 tables) along with his agenda etc ... during the week, whilst
    travelling from client sites to client sites, the sales person can write
    reports about his visits (these are indexed in the database), amend the
    client/prospect database (add contacts, amend contacts, add notes ....) upon
    his return to the head office, the sales person 'plugs' his netbook to the
    network, press the 'synch' button and pooof all by magic all changes made
    locally are merged with the central database, taking into account any
    changes made to the central database. Once this merge process is finished,
    the sales person netbook is refreshed with a copy of the new database
    fragment he deals with.
    Mladen Gogala wrote:
    Your sales manager should get acquainted with something called "the Internet".
    The sales people should only log into the website providing service, instead
    of carrying the database around. Also, they shouldn't be carrying "notepads",
    "laptops" or any such contraption, they should be carrying iPads. Make sure to
    spell it right, "iPad" is not the same as "iPod". Your sales manager has a lot
    to learn.
    There's no reason to restrict it to iPads. Notepads, netbooks, Android pads,
    tablets - even a smartphone could serve the purpose. There's no reason to
    shill for Apple.

    --
    Lew
    Ceci n'est pas une pipe.
  • Mladen Gogala at Jan 10, 2011 at 2:01 am

    Lew wrote:
    There's no reason to restrict it to iPads. Notepads, netbooks, Android pads,
    tablets - even a smartphone could serve the purpose. There's no reason to
    shill for Apple.
    Well, I live in the Big Apple, most of the sales people that I know
    carry iPads. As for the gadgets, I am not a big fan of Apple Corp. I
    own a Kindle and I mostly use Linux. I do love the Big Apple, though.
    It's a beautiful city.

    --
    Mladen Gogala
    Sr. Oracle DBA
    1500 Broadway
    New York, NY 10036
    (212) 329-5251
    www.vmsinfo.com
  • Craig Barnes at Jan 9, 2011 at 5:01 pm
    for the list.

    ---------- Forwarded message ----------
    From: Craig Barnes <cjbarnes18@gmail.com>
    Date: 9 January 2011 16:48
    Subject: Re: [NOVICE] Db synch - need advice
    To: Didier Gasser-Morlay <didiergm@gmail.com>


    Didier,

    No p

    Yes, please do let us know how you get on.

    Thanks

    Craig

    On 8 January 2011 23:03, Didier Gasser-Morlay wrote:

    Craig,
    Thanks a bunch for the pointer I'll jump onto it after a few hours of sleep
    (it's just over midnight here).

    Thanks again for your help. If you are interested I can report back in the
    near future if this project goes anywhere.

    Didier



    On 8 January 2011 23:10, Craig Barnes wrote:

    Hi Didier,

    Fair enough, this was the better use case for the dbase based system that
    I used back then. That was implemented within the application using a
    change log and complex rules for duplicate checking.

    I just recalled a replication methods<http://www.postgresql.org/docs/9.0/interactive/different-replication-solutions.html>reference table in the postgresql manual. According to this, Multi Master
    replication is possible using Bacardo <http://bucardo.org/>. Looks like
    an interesting project, worth looking at it to see if it could fulfil your
    needs.

    Regards

    Craig


    On 8 January 2011 19:44, Didier Gasser-Morlay wrote:

    Craig,

    Thanks a lot for the insight, I am fully aware of the risks. that being
    said, in THIS instance, I believe it can work because of the low volume of
    updates. It is indeed a new system in which I can do pretty much what I
    decide/need to do. This is why I am inquiring for strategy suggestions or
    possibly existing tools (one can always dream:))

    As said previously, online access would be a preferred option (even a
    simple vpn would do!) but decent acces speed is not guaranteed by a long
    stretch! the sales guys are operating in fairly remote area (even lower
    mountains) where the GSM 3G is not available and won't be for a few years at
    best. hence the need to collect as much data as possible and sync when a
    connection is available.

    Thanks again

    Didier
    On 8 January 2011 19:48, Craig Barnes wrote:

    Hi Didier,

    A few years ago I worked with a Commercial Off The Shelf CRM application
    that supported Database Synchronisation. It worked but was prone to all
    sorts of difficulties including data loss and complicated conflict
    resolution processes. Relational databases just don't lend themselves to
    this kind of work-flow.

    My personal opinion would be to advise against synchronisation in this
    way in favour of online access.

    Non relational databases work better with offline data, and there is
    some implementation in in Evolution, but I don’t think this allows for
    separate data sets.

    Is there an existing system in place that wants to be augmented, or are
    you looking for a new system to implement?

    Hope This Helps.

    Craig
    <-snip ->
  • Didier Gasser-Morlay at Jan 10, 2011 at 5:31 pm
    Lew,

    With all due respect, a local database with sync will get them speed and
    information reliable enough to do their job; these are not portable nuclear
    missile launch pads needing up-to-the-minute updates, these people are
    travelling sales persons needing access to information, in remote areas
    where GSM 3G, edge, 3G+ ... is often a distant dream.

    Not everyone lives in the big Apple you know ?
  • Didier Gasser-Morlay at Jan 10, 2011 at 5:45 pm
    I realise that for some reasons my answers were not always send back to the
    list. Apologies. Just wanted to thank all of of you for the help &
    suggestions. I'll report back in a few weeks once something is in place.
  • Chris Browne at Jan 10, 2011 at 6:25 pm

    Didier Gasser-Morlay writes:
    Instead of reinventing the wheel I thought I would ask what you all
    reckon  would be the best strategy
    Unfortunately, "simple and reliable" are commonly not notably compatible
    in this regard; it's quite likely to be impossible.

    "Simple but wrong" is pretty likely to happen.

    "Complex and wrong" is also pretty likely to happen.

    "Complex and reliable" is the best scenario I'd realistically expect to
    be able to hope for.

    The much easier "technical answer" is likely to be to improve
    connectivity to the central database. Wireless connectivity is getting
    good enough that it's quite likely to be cheaper to get the sales guys
    data plans for their "Rocket Sticks" rather than to redesign the
    application.

    The trouble is that you are certain to *badly* need a bunch of business
    policy as to the default handlings of conflict resolution. (e.g. - what
    does the system do if 2 salescritters, who are both offline, try to sell
    the same lots of product?)

    That'll be a big business fight all the way through, impinging on your
    technical solution, even though it's not "technical stuff."
    --
    "cbbrowne","@","gmail.com"
    http://linuxdatabases.info/info/wp.html
    As of next Tuesday NCOMPLR will no longer open-code arithmetic statements.
    Please update your programs.
  • Didier Gasser-Morlay at Jan 11, 2011 at 9:54 pm
    Chris,

    Thanks for your input and analysis. Something I did not make quite clear,
    and I apologize for this, it that although there are sales people, I am not
    trying to deal with the sales process itself, but the sales support function
    - Pre (presentation of product line to potential client, getting an
    appointment etc ...) and post sale, ie when the customer is a regular
    customer. ie documenting the sales relation, regular visits, quotes etc ...
    issues reported with the products and how the company handled it etc ...

    So the problem at hand is at least an order of magnitude easier than what
    you imply in your example : (e.g. - what
    does the system do if 2 salescritters, who are both offline, try to sell the
    same lots of product?)

    In instances like that, my answer would be 'No can do, wait a few years
    until GSM 3G+ is available where your sales people travel or buy now a sat
    link'. but we are in a much simpler scenario which requires a small set of
    business rules which can be enforced.

    I am already in the process of writing my own mix of front-end and Back-end
    systems that is aiming for "not too Complex and reliable" :) .

    Didier
  • Chris Browne at Jan 11, 2011 at 11:25 pm

    Didier Gasser-Morlay writes:
    Chris,

    Thanks for your input and analysis. Something I did not make quite
    clear, and I apologize for this, it that although there are sales
    people, I am not trying to deal with the sales process itself, but the
    sales support function - Pre (presentation of product line to
    potential client, getting an appointment etc ...) and post sale, ie
    when the customer is a regular customer. ie documenting the sales
    relation, regular visits, quotes etc ... issues reported with the
    products and how the company handled it etc ...

    So the problem at hand is at least an order of magnitude easier than
    what you imply in your example :  (e.g. - what
    does the system do if 2 salescritters, who are both offline, try to
    sell the same lots of product?)
    As one of the folks that work on replication for Postgres, part of where
    I come from is in attempting to have solutions that attempt to be
    reasonably general purpose.

    It sounds as though you might have a set of business policy that lends
    itself to creating a distributable system. If that's the case, then
    that has a few implications:

    a) It's a good thing for you; if the things that come up are readily
    resolvable, then you can build a system on that basis.

    b) It doesn't lend itself to being a general purpose solution, which
    somewhat limits community interest.
    --
    select 'cbbrowne' || '@' || 'linuxfinances.info';
    http://www3.sympatico.ca/cbbrowne/lsf.html
    "A ROUGH WHIMPER OF INSANITY" is an anagram for "INFORMATION
    SUPERHIGHWAY".

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJan 8, '11 at 6:26a
activeJan 11, '11 at 11:25p
posts14
users7
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase