locked
Central vs Local Database RRS feed

  • Question

  • I am putting the architecture of an application suite intended to be used by resturants.

    The suite consists of  an application to be used by the call center, application(s) to be used by branches, and application for the headquarters.

    Data is shared between the applications. Sales in the branches should be used to generate reports at the headquarters, orders issued by the call center should be viewable and at the branches and its state is updated by the branches, etc.
    Some data (such as the menus data) are shared between branches but should be edited by someone with a high administrative privilege.
     
    The question here is whether to use a central database where all applications connect to this database, or to use a local database at each location (branch, call center, headquarters) with some sort of synchronization between all branches.

    A central database allows for an easier interaction and share of data between branches, and allows for a better control over administrative permissions and authorization process.

    If the central database architecture is used, a fall back mechanism has to be used in case of connection failure. Data should be stored locally (with some restrictions on the available operations) and then synchronized with the central database when connection is restored. Perhaps this means that the complexity of using a central database is not very much less than the use of a local database from the start.

    I need to know your opinions about this case, and the possible design and architecture patterns that could be used in this case or that has been used in similar cases.
    Sunday, July 6, 2008 2:47 PM

Answers

  • I think the term 'database' has been used when perhaps 'persistant store' would have been better, i.e. storing the XML in a file is a persitant store.
    Wednesday, July 16, 2008 7:06 PM

All replies

  • You will probably need a database for your HQ, one for your call center, and a local database at each restaurant. You should definitely be looking at a messaging-based design for getting the right data to the right place at the right time with the right transactional semantics.

     

    It's hard to describe the entire architecture here in comments, but you'll probably have several kinds of messages:

     

    • OrderReceivedMessage - which should be handled by the restaurant closest to the shipping address that has a delivery service.
    • ProductUpdatedInMenuMessage - which should be published by the HQ, subscribed to by all the branches. Need to think about whether pricing data should travel in this message or separately.
    • OrderCompletedMessage - should be sent by the branches to the HQ after the customer has paid.

     

    Reports should be handled separate from the regular transaction processing so as not to interfere or hurt performance.

     

    NServiceBus can be used to faciliate this kind of architecture. If you want more info, you can email me: email@UdiDahan.com

    Sunday, July 6, 2008 7:50 PM
  • If you're not too worried about going the "SOA" route you could look at simple database replication or even the synchronization framework.
    Sunday, July 6, 2008 9:05 PM
  • Thanks for your replies.

    I intend to use messages for communication, But the solution of using multiple databases and the use of messages for communication AND synchronization between databases assumes that the connection is always alive. This is not always the case, specially here in Egypt where bad internet connections are something very usual.

    Using a central database would be fault tolerant to a great extent. When somebody updates the data (orders mostly), he can send a message to other interested parties (most of the time it is one party). If the message doesn't arrive to one of them due to a connection failure, when the connection is back parties which were offline would connect to the database to get the latest version of the orders they are following.

    For the report generation I think a special server  for business intelligence operations would be used in case of complex reports. That server would be
    connected to the central database server with a high bandwidth connection. I didn't study the required reports and their complexity yet but there is a possibility that reports would be generated by the same central server hosting the central database if the business intelligence operations were simple. The expected size of the database is not large, so database operations will not be time consuming, specially if a good indexed design was used.

     
    Monday, July 7, 2008 4:09 PM
  •  Muhammad Adel wrote:
    Thanks for your replies.

    I intend to use messages for communication, But the solution of using multiple databases and the use of messages for communication AND synchronization between databases assumes that the connection is always alive. This is not always the case, specially here in Egypt where bad internet connections are something very usual.
     

     

    I don't understand where the assumption about the 'connection is always alive' has come from?

     

    Monday, July 7, 2008 4:24 PM
  • Hi Muhammad,

      

        In this case and as Udi Hadan said there's the possibility of using a Message-based solution, because in case of a disconnected environment you'll need to continue working, but in my opinion you have the following choises:

    1. Centralized Database and using a Message-based solution to solve the disconnected problem
      • In this case you'll be always certain that the work done while offline will be presisted
      • The drawback is that if there's a central database you'll have a problem working while offline, because if there's no local database how will the Branches be able to show the Menu Data?
      • So I think this isn't the right choice.
    2. Centralized Database and Local databases for caching purposes
      • Here you'll have always the possiblity of working while online or offline without any problems of missing data
      • The drawback is that you'll need to use a Syncronization mechanism to do the transitions of data from the Local Database to the Central Database, and this can be an issue, but you can solve it using Microsoft Sync Framework, a Message-based solutions and so anytime you make a change you'll also send the informations to the Central Database
      • Being completely clear with you, this is a solutions, but I like more the usage of the Microsoft Sync Framework, and I think this is the right choice.

    Hope this answered your question.

    Tuesday, July 15, 2008 11:55 PM
  •  Nuno.Godinho wrote:

    Hi Muhammad,

      

        In this case and as Udi Hadan said there's the possibility of using a Message-based solution, because in case of a disconnected environment you'll need to continue working, but in my opinion you have the following choises:

    1. Centralized Database and using a Message-based solution to solve the disconnected problem
      • In this case you'll be always certain that the work done while offline will be presisted
      • The drawback is that if there's a central database you'll have a problem working while offline, because if there's no local database how will the Branches be able to show the Menu Data?
      • So I think this isn't the right choice.


    I would store the menu data in an xml format locally on every machine in the branches. This version will be updated every time the application starts or every period of time or based on an update message from the central application, and it would be used in case of losing connection with the central database.
    Wednesday, July 16, 2008 6:32 PM
  • I think the term 'database' has been used when perhaps 'persistant store' would have been better, i.e. storing the XML in a file is a persitant store.
    Wednesday, July 16, 2008 7:06 PM
  • Hi Muhammad,

     

          Using a XML format to locally presist the data information for the menu is the second approach that I suggested, and the usage of the Database can be any kind of presistent store as pkr2000 mencioned.

     

          I think this is a nice solutions, and the usage of a XML file only depends on the amount of information that will be stored. I recommended you the Microsoft Sync Framework for ADO.NET because it installs the SQL 2005 CE that is really lightweight and fast and even for large or small amounts of data "works like a charm".

     

    Hope this helps you.

    Wednesday, July 16, 2008 10:18 PM