Search code examples
sqlpostgresqlreplicationdatamodel

Database design for a multi branches POS system

I am building a POS system that support multi branches.

The system is going to support these features.

  • Each store should have a local database for it's own inventory list and invoice. (Local database to avoid internet failure).
  • There is a reporting DB that contains information of all shop (inventory, invoice, etc), the reporting DB can be async to shopDB.
  • Each shop contains a unique shop code to indentify the record ownership, also a part of key (to avoid issue with primary key).
  • Shop system can query Reporting DB for inventory list on other shop (customer can place an order, shop may query for full inventory list and get other branches ship them the inventory).

Currently I am building the system with Java, PostgreSQL and Cayenne, but I am open to change the DB or ORM tool in case there is any technology limitation.

I tried to read a lot with Replication and Clustering, but it doesn't appears to suit my need.

Any clues on what I should look for ? Or should I build the replication on app layer instead of DB layer ?

Solution

  • The thing that strikes me here is what happens when shop A sells inventory for shop B while shop B sells the same inventory?

    why cant the application access other shops dbs?

    have you read about federated databases - http://dev.mysql.com/doc/refman/5.1/en/federated-description.html

    http://en.wikipedia.org/wiki/Federated_database_system