Need Some Advice

[dcarlson]

I'm currently in the process of creating a database that will be located in some of our branch offices and need to be able to synchronize our headoffice database with data from the branches. Right now I'm working on an Import/Export routine using persisted recordsets and looping through them to validate whether or not to be imported. I can see some problems occurring with deleted records and I know that ideally the database should be on some sort of network but our LAN is not accessible by our branches.

My question finally is what would be the best way to Import/Export data? Persisting recordsets are easy but I'm wondering if there's a more efficient way.

[ysa1441]

hi,
u can use the unc:
UNC - universall naming convension
anyway, in short :
example:
"\\servername\compname\sharename\"
or
"\\usrname\sharename\"
i hop i helped
bye,
yair

[Gen-X]

What database were you thinking of using?

Ok are the branch PCs in question on an Intranet?

If so map the database drives to your central Server. This will allow you to view all dbs in one virtual location, making the coding simpler. Use this approach at a client's site for a national sales db with good results.

Oh make sure that you can share the new mapped drives, My Computer -> Drives -> Sharing

[PaulLewis]

If your database design cannot be altered much, and if all of your remote sites simply get an entire new copy of the central database daily, then I can see why you are asking for advice as that is a curly one.

If you can alter the DB design a little (ahem , ok you caught me - alot), then you should implement a transaction table.

A transaction table will record for you the changes to data that the users asked for. It's not as bad as it sounds once you get into it - especially if make it generic.

This way, CRUD transactions are handled locally on the site (which updates their database) and then the exact same transaction can be executed on the central database as well.

If the transaction said to delete a record, then it is processed at central just as it was at the site, and hopefully, with the same effect!

This won't be very easy if you have legacy systems accessing the database which you cannot change.

I have no idea if it helps you but there you go

Regards
Paul Lewis

[Gen-X]

There is an inherant problem with this kind of architecture though.


Site 1
Modified Record for "Fred Smith" changing his address

Site 2
This is the accounts division and sets the overdue amount for "Fred Smith".


In the transaction log we have 2 change requests.... If the address is changed then the changing of the overdue amount *might* cause the new address to get lost. If the overdue amount is changed then changing the address *might* lose the overdue amount.

Even if you were to process these 2 records in the order they were received one of them didn't realise what the other one was going to make a change and therefor the changes can get lost.


Now if you are absolute, categorically and TOTALLY sure that one site cannot modify the data of the other site then I would simply use something like SQL Server and impliment TRIGGERS to automatically populate a transaction file to be exported and sent to the head office.

[PaulLewis]

Gen-X,

I guess I forgot to mention that transaction table relies on foriegn unique keys on all other tables. I also routinely reply on integrity contraints to be enforced (in Oracle) or relationships to be created in Access (although I never use cascading operations - habit and fear I think).

I guess I forget to mention that because it's automatic for me.

IN your example though, no solution would be simple because if one site deletes the customer and another attempts to update it, who wins? Transaction table allows you to process all add's and updates, and only process deletes where there was no other mod (as an idea).

Any other system relies on the VERY clever importer code to import from each site, work out what happened (add, delete, modify), then either do it, OR store a transaction record to do it later since you can't risk deleting someone that just about to order 1000 copies of your shareware app

All each of us can do is relay what we know or believe works... I know transations works best for such a scenario but the proof is always in the trying

Cheers
Paul Lewis

[PaulLewis]

Ummm,

I think not. I mean, there's alot of discussion, but I when I read the original request for advice, I understood it as meaning that the remote sites do not have access to the central database and that dcarlson plans on using persistant recordsets.

I see no reason why a persistant recordset consisting of all transactions the site generated would be too hard to cope with.

I see loads of reasons why multiple persistant recordsets would be hard to cope with (especially if there are multiple relationships between tables).

The art is in designing the transaction table... if you go that way

Cheers

Paul Lewis

[Gen-X]

Your right.

Though I think the first thing to establish is if it IS possible for different sites to change the same records.

If it is then that would indicate attempting ANY form of remote synchronization was a waste of time (as you said above which was why I wrote it).

Once you have established that there isn't any overlap the Transaction file would be a perfect way to keep everything up to date on the central server.

It would also cut down on the throughput if only the changes were sent in the transaction log although you would have to make sure there is an ability to either :

1. Re-request a transaction log if it went missing
2. Ability to generate a FULL dump should things go wrong

[PaulLewis]

Of course, it doesn't mean we're right, but solidarity breeds confidence!

I do disagree though with your comment "attempting ANY form of remote synchronization was a waste of time" as the beauty of the transaction table (whether it is an external log file or a table in the database) is that you can rebuild your entire data set byt re-processing the transaction records in the same order that you process them "last time".

Does this mean you have to waste space storing everything twice? YES more or less it does.

It also makes life hard if you have multiple Long Binary or BLOB fields in your database. Having a generic transaction table will only work well if you are in control of the database structure (obvious but sadly not always the case!)

Anyway, dcarlson has "some work to do" however he ends up doing it... I'd like to say it'll be fun... maybe it will

Cheers

Paul Lewis

[dcarlson]

Quite the discussion, the database is in FoxPro 5.0 and I have total control over the structure. I don't have to worry about the branches modifying the same records because each branch will be taking care of their own records only and the recordsets will be emailed to headoffice.

As for the transaction table, I understand the concept but what would the table consist of in the way of fields?

[PaulLewis]

It's over to you and how much you like parsing as opposed to delving through recordsets.

VB makes it easy to walk through fields and recordsets so I would go that way.

On the other hand, if you have very simple data in all your other tables then parsing is an option.

In the transaction table you would have for starters
unique ID (autonumber or equivalent in Fox)
transaction date/time
transaction code (more later)
processed date/time
process code (result of processing, i.e. success, failure, etc)
transaction detail (single field containing all details )
OR
transaction field[1..20] (multiple fields - more later)

For example, in an order preocessing database,
transaction code might resolve to a number of operations that the gui initiated. If there are a dozen buttons on the gui where the user can cause a change to the data in the database, then you could perhaps have one transaction code per button. (note that this lends itself well to totally encapsulating DB functionality within your forms)

In the customer detail form, you might have a code for "delete customer", "modify customer" and "add customer". IN most cases, update and add are the same depending on your multi-user record locking strategy. IN any case, we have three transactions.

Now all the data you need to run that transaction can be taken from the form and pumped into the transaction table. If one of the form fields allows any text whatsoever, you will have to be careful about trying to pump it all into one field for obvious reasons...how do you get it out again without causing yourself some headache.

So assuming the code was for delete customer, the first transaction field would contain the unique id of that customer and that should be all you need to put in there.

For update, you would need enough fields in your transaction table to cover all the fileds on the form. This DOES lead to a large increase in the database size (not that it could be a different database or even a random access file).

The biggest part of the job is to take the data processing rules embedded in your form and place these in a separate standalone module that does not need the form to drive it.

The form adds to the transaction table, then triggers the transaction module to process the transaction. When the transaction is complete you can respond to the user (note that this makes it easier to handle asynchronous data processing if it ever became useful to you...)

Now all this sweat has given you the ability to apply those same transactions on another system. the other system doesn't even need to handle them the same way and in fact the database structure on the other system need not even be identical. In your case they are the same and will be handled identically so you are simply going to have to add a module to your central database to import everyone elses transaction tables (ignore the processed date as you will want to populate that yourself).

Then, you could sort all the transactions any way you wanted. You could decide to handle all adds, then all updates, then all deletes or handle them by order of transaction date or whatever.

When handling these transactions, the thing to be aware of as Gen-X pointed out is the multitude of "what if " situations.

What if a customer was deleted, then added then updated at one of the sites? (i.e. a user deleted a customer by mistake, then re-added them and later updated them).

But you have said it is not a worry in your situation so you can just process the transactions in the order they came in if you like.

I know there's alot more to it, and if you are thinking about trying it out I recommend a prototype first. You have to get your feet wet before you know if it is a method you want to use (and live with).

And I know I sound like an evangelist - you'd almost think I had something to sell you... Well, all I know is that it has worked for me and my colleagues - and I am not the inventer of the idea - just a happy user I would say that this type of handling (was it called batch processing in years of old?) has been around since man first invented bookkeeping

Good luck

Paul Lewis

(So ends Chapter 2 in my quest to become the poster with the longest post...hehe)

Right didn't realise the branch offices don't connect to your central server, therefore mapping remote drives, or using SMS or PC anywhere isn't going to help you damn.

Ok what exactly do you need to extract from the Branch dbs, transaction records, sales, new customers, or something else? Do the branch dbs contain updated info on the same customers or something similar?

How about adding a branch id field to your databases, that would at least avoid over writing data?

Still not quite sure what you are really asking? Do you need to consolidate transaction records, e.g three branches with sales for the same customer???????

[dcarlson]

That's a pretty long post. Hehe. You gave me a good understanting of what you meant and if I implement this hell of a solution I'll definitely look back to this. (by the way I found out today that we may be getting web server, if so problem solved.)

I don't know if this is over simplifying or not but how about if each time I insert, update or delete records, I store the query string in a table with perhaps a transaction date and simply iterate through the queries. It may redundantly update records that may be updated a few times in between exports however the export/import should be done on a weekly basis and the throughput isn't an incredible amount. We currently have three or four people at headoffice entering all transactions for each branch and that's far from their full-time jobs.

Thanks for all your help I appreciate it, won't you be pissed for all that thought and typing if we do get the web server. (how do you smily here???)

[Gen-X]

Now that we know each branch will not effect the data of another and that the "head office" is like a glorified repository combining all this data the answer is simple.

The transaction table is overkill in this specific situation


The solution lies in several factors :

1. How often do they update records?
2. How large are the tables being synchronized?
3. How often are you going to do this synchronization?


From what you have said you will do it once a week, and that there really isn't very much data to come across.


Therefor what I would consider the most efficient, and most robust method would be to simply dump the entire table and simply overwrite the main server using that.


It is much quicker to do the following : (in SQL Server 7)

DELETE FROM Table WHERE BranchId = 6 ' All records
BULK INSERT Table FROM 'filename'

Providing the "dump" of your files is done in the correct format and you are only doing this with say 10,000 records this will be much quicker than any kind of other algorithm.

[dcarlson]

The only problem with that is there is already data in the headoffice database that goes back five years. The purpose of the branch databases is so they can track and manage their own files without having to rely on headoffice. The branch databases will starting almost from scratch, except for stuff like client contacts.

I'm still wondering about saving the query strings with a date and iterating through them by date.

[PaulLewis]

The purpose of my posts were only to let you in on at least one other way of looking at the problem. Now that you're thinking about it, you will come up with your own hybrid solution.

I also write it because by using the search facility on this board (as I have done several times) future enquirers may come across the tomes of knowledge and find it helpful (or they might laugh of course )

As it happens, if you are using SQL statements for all transactions then your job is significantly easier if you wanted to do any sort of transaction table.

Also, the observation that it might be overkill is valid, however as a programmer, you might want to be thinking about the things you can "practice" or learn on this project that may help you in the next or in future employment opportunities.

A stale programmer is one who has not changed their way of looking at things in the last day or two So stay freash and keep changing your mind lol

[dcarlson]

Thanks for all the help Paul and Gen-X.