Please Help me out in the Design of Postgres Database for the criteria
1. We have 20 Area Offices -- each Area Office to have another 20 branches -
2. One Central Server at Head Office..
Transactions are done on area basis .. Queries/Updataios/Additions are within the area office. Even Master files -- like customer data are specific to area. Transaction indexes need not be on the entire enterprise they can be limited to Area.
Kindly suggest how I should Design. at the Central Server
-- Have one Large Data Base ..for the entire enterprise
Updates/Inserts will NEVER be done at the central server?
One approach would be to set up dblinks from area offices to main office server, and use insert/update triggers for each area office table to insert/update the new/altered records in the main office server. You will certainly want to investigate tuning, especially at the main office server.
Use guids instead of sequences for unique or pk fields, or add a location code field to the sequence for the PK.
Review Statement-Based Replication Middleware, Asynchronous Multimaster Replication, & Synchronous Multimaster Replication, and possibly, Data Partitioning, here.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert