Обсуждение: Db synch - need advice
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
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
On 1/8/2011 1:26 AM, Didier Gasser-Morlay 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.
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
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
So thank you for the bit of advice but no thanks
On 8 January 2011 19:22, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
On 1/8/2011 1:26 AM, Didier Gasser-Morlay 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.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 Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com
(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.
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.
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
---------- 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 <didiergm@gmail.com> 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.
DidierOn 8 January 2011 23:10, Craig Barnes <cjbarnes18@gmail.com> 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 reference table in the postgresql manual. According to this, Multi Master replication is possible using Bacardo. Looks like an interesting project, worth looking at it to see if it could fulfil your needs.
Regards
CraigOn 8 January 2011 19:44, Didier Gasser-Morlay <didiergm@gmail.com> 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
DidierOn 8 January 2011 19:48, Craig Barnes <cjbarnes18@gmail.com> 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 ->
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
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."
> 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."
didiergm@gmail.com (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.
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
didiergm@gmail.com (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".