Exchanging data between branches - Database Design Help

[Alwin]

Hey All!

I am currently designing a database model for (at first sight) a simple order entry program. The problem I'm currently facing is the exchange of data between the databases of each branch of the company. The company consists of five branches where each the same program and, hence, same database is installed. The data between these branches have to be exchanged (i.e. customers, users).

Now my question is, how should I design the database that it will be possible for exchanging data between the branches and still the primarykey remains unique and references to this primarykey still work (i.e. entity Order refers to a specific Customer)?

For example with the entity Customer and its primarykey CustomerID. Branch A add customers in its database, and also branch B add customers in its database. Both databases contains CustomerID of value 2, but the customer of each database with this ID represents a different customer (sounds logical, right?). How to exchange the data from branch A to branch B?

One limitation I have to add, there's no high-speed connection between the branches. Let say the exchange should still be done with a dial-up 56k modem (yes, it sucks, i know, but I am currently doing a project in a third world country... :o )

I hope I described my problem clear, please let me know if you need more information... thanks in advance!

[sashi]

Hi Alwin,

i think i clearly understood whatever ur concern is.. well.. do correct me if am wrong.. redundancy?? is that is my fren??

u mentioned that u need to install same front-end as well as the back-end (database) on each machine on each branch.. rite?? well.. my suggestion is..

since ur thinking of performing the data exchange over an internet line which is connected to each branch.. since redundancy is the main concern here.. why don't we look or take a diff approach which will reduce the work burden.. what am trying to say is n ask is.. 1st of all have u tried looking at other options as well?? maybe like the development language itself?

confused ah?? not yet my fren.. you know what i would do if i were u?? i would stop thinking about normalizing the database design instead.. well.. since we have an internet connection present here.. i would make a web based system my choice..

why not thinking of designing a web based system.. maybe .asp/.php.. i these case.. it will reduce your burden when it comes to troubleshooting your application.. take a moment to think.. you have 5 branches to look after.. what will happen at the end of the day?? you will be tied up btw these brances my fren..

all these issues can be by passed when working with web based application.. sisnce you have 5 branches.. am sure u must have an HQ somewhere.. rite?? why not you place the master database at you HQ and let all your clients (branches) to access the database.. think abt it?? wouldn't it save you alot of valuable time??

the only thing you have to concern it the database itself.. create a relational database will all the primary key n secondary key correct n it should work fine..

well.. this is my suggestion thou.. but it the meantime i'll try to find some solutions for your question above.. maybe it's time to pick-up my phone receiver to call few of my old frens to get an idea..

give it a tot.. to me it makes sense.. how abt you?? good luck to u my fren..

[Alwin]

Hey Sashi,

Thanks for sharing your thought, but I don't think it would be a practical solution for having the web-based system. This will cost me a huge drop in performance (which is not affordable) due to the really slow internet connection. It will take the clients a couple of seconds every time to insert/update/delete a record. So the only option in this case should be a separate database at each client and these should replicate every hour or so...

There's indeed a HQ which will contain the master database. But I want to replicate all the customers / users etc etc across the other databases as well...

Thanks again...

[sashi]

Hi Alwin,

emm.. let me suggest using the Remote Database Object (RDO) method.. when it comes to replicating non-duplicate record pls use the DISTINCT keyword in your SELECT statement..

hope the above method works and good luck to u my fren..