Architecture Question - One Central Database and Many Different Programs Accessing It

Question

I am designing a program that will build and maintain a database, and act as a central server. This is the 'first stage' of a grander plan. Coming later will be 3-5 remote programs built around the information put into this database.

The requirements are:

1. The remote programs must be able to access the information in the database.
2. The remote programs must be able to set alerts when information in the database changes.
3. The remote programs must be able to request the central server to go out and fetch new / different data.

So, the question is this: how do I expose this data and events to the outside world? My two choices are:

1. Have them communicate directly with my 'server' application. This seems easier to:
   • do event notifications (although I suppose I'm probably missing something in SQL).
   • It also seems like this is more 'upgradeable' - that is I don't need to worry about the database updating and crashing all my remote programs because something changed. I can account for this and transform it the data to a version the child program will understand.
2. Just go ahead and let them connect directly to the database.
   • This nice thing about this is that it's solved. I can use LINQ for SQL. The only thing the main server application needs to do is let the remote programs know where the database is.
   • I'm unsure how to trigger / relay 'events' for field changes in a database over different programs that may or may not be on the same computer.

Forgive my ignorance on this question. I feel woefully unprepared to ask it, but I'm having a hard time figuring out where to get started with this. It is my first real DB project :-/

Thanks!

Answer 1

If the other programs are going to need to know about updates to the database, then the best solution is to manage all db updates through your server application so it can alert clients of the changes. Otherwise it will be tough for the clients to be aware of changes to the db. This also has the advantage of hiding the implementation details of your storage solution from the clients, so you are free to change databases, etc...