Writing an extension to an off-the-shelf solution...

[JohnnyD]

Hi,

I need to develop a system of batching up a load of outstanding orders based
on how many people we have working and how many hours, etc.. The existing
data is within an off-the-shelf Visual FoxPro database which is soon to be
moved into SQL Server 2005. I am planning on writing my program to use SQL
Server 2005 as well.

There is an order table within the existing database with a primary key
'orderid'. My system will need to select orders based on various parameters
and then group similar orders together for a number of packers. This will be
done up to a week in advance (because of the nature of our products the
customers need to be emailed in advance).

I need some way of making sure that if an order gets cancelled in the
existing database, it doesn't end up staying in my new system and getting
delivered.

My thoughts so far are that:

A - I either need to use the existing database orders table as my only
source of order data, or

B - Create an orders table in my new database and somehow keep them in sync
with each other.

The problem with A is that if an order gets cancelled, it will just
dissapear from my delivery system and then the batch that contained that
order will no longer contain enough work for that person to do. There would
need to be some kind of notification so that it could add another order into
that batch - the only way I can think of doing this would be constantly
checking the existing orders table for changes which wouldn't be very good
for performance.

The problem with B is the synchronisation. At least with this method, If
I've added all the orders in the existing database into my database, I can
refer back to the status of each one at some point before they get delivered
to see if they've been cancelled.

If anyone has any advice on how to go about things like this I'd be really
grateful. It's quite a difficult thing for me to get my head round.

Thanks,

John.

 

[Gregory A. Beamer]

If anyone has any advice on how to go about things like this I'd be
really grateful. It's quite a difficult thing for me to get my head
round.

If you have time, the "correct" method is to set up a front end for the
original app. You then get the users on the new front end.

Next step is to have changes written to both databases. This is a pain,
but allows you to move off the FoxPro to the SQL database without an
elaborate.

When you are confident of the data in SQL Server, shut off FoxPro.

There are ways to sync, but you end up with a two-way sync as you move
users over, which is a pain. It also means writing the sync in FoxPro to
keep changes up to date in SQL Server.

If you were moving from Oracle to SQL Server or one instance of SQL
Server to another of SQL Server, there are great tools out there to sync
things. But I know of none from FoxPro to SQL Server. One may exist, but
then you have to figure if it is worth budgeting, esp. if the switchover
is relatively quick.

By separating UI out, you can control the save to the database(s) and
then shut off the updates to FoxPro.

If FoxPro is permanent and has to run side by side, ouch. Your only
option here is a two-way sync. Or at least the only one I can think of
now.

Peace and Grace,

--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[JohnnyD]

Hi Thanks Gregory.



The vendors of the off-the-shelf app have actually released a new version
which uses a SQL server database. I am planning the upgrade right now and
hope to get using it within the next week.



Does this change things at all? Both this database and the one I want to
write will be using SQL server, albeit different instances. I can't imagine
they'd continue to support their application if I were to start adding
tables to their database so as far as I can see, the two databases would
still have to run separately but both using SQL server.



Many thanks,



John.

 

[Gregory A. Beamer]

Does this change things at all? Both this database and the one I want
to write will be using SQL server, albeit different instances. I can't
imagine they'd continue to support their application if I were to
start adding tables to their database so as far as I can see, the two
databases would still have to run separately but both using SQL
server.

Regardless of controls, third party addins, etc., I still advocate the
"app == logic to solve business problems" methodology, with the database
as a persistant store and the UI as a faceplate. It is very flexible and
adheres nicely to proper separation of concerns. It also forces some
inversion of control, when you add testing, which makes it quite easy to
maintain. There is a learning curve at first, but it pays off huge
dividends.

Peace and Grace,

--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[Gregory A. Beamer]

Does this change things at all? Both this database and the one I want
to write will be using SQL server, albeit different instances. I can't
imagine they'd continue to support their application if I were to
start adding tables to their database so as far as I can see, the two
databases would still have to run separately but both using SQL
server.

I only finished half of my answer and then hit send. DUH! ;-)

As far as how to get from here to there (legacy to new), the advice may
still apply, depending on what the new control does for you and how easy
it is to get rid of the FoxPro UI.

Peace and Grace,

--
Gregory A. Beamer (MVP)

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************