Architecture for one database and several apps

Question

If five applications are using the same database, and these apps need to be released at different times, what type of architecture or configuration accommodates that scenario?

The main concern is: Any of the five applications are allowed to change the database, which can then break any of the four apps in production.

Answer 1

Sanitizing data on the lowest level usually means doing some referential integrity and primary and foreign key job. This has to be done in the stored procedures (so that stored procedures should know that value from sequence/generator A_seq should be put into primary key of table A, and this should be also put into A_id into table B, etc). There can be for example some stored procedure entitled SavePurchaseHead, SavePurchaseDetails, GetSegmentForCustomer, GetCustomerBalance, GetOverdueLevelForSegment, etc. This is done mostly to hide logic of INSERT, UPDATE or JOIN sql instructions inside database.

Now imagine where you will want to put some business knowledge, like “customer overdue 5000 USD cannot make a new purchase until he pays with cash”? Stored procedures are usually bad idea for such calculations – the better solution is to have some business service like MakeNewPurchase, that will take customer data and purchase data, and do business stuff. This service may probably start with getting information about customer segment, than get customer balance and compare it with this segment overdue level. If everything will be ok, procedures like SavePurchaseHead and SavePurchaseDetails will be executed.

So you will have some levels of knowledge: client applications will know only that making purchase requires you to call service MakeNewPurchase. They will talk in “business language”. Plus – important changes to business logic will have to be made only in one place (business logic layer) instead of FIVE apps. Than business logic layer will know how the business is made, but will lack knowledge about how UI is composed and will lack knowledge about how exactly data is stored and optimized. Database will have only knowledge about data and how it is stored. This will divide responsibilities between all layers, and while working I multi developers team it will help you to maintain some order, through tiny interfaces. And – as mentioned before – one application won’t hurt other by “breaking” the data.

Two last things – when I say “service-oriented middleware” I do not mean any hype. As I wrote before – SELECT and UPDATE should be on DB level. “Middleware” or “Business layer” (no matter how you will call this) should be on more business oriented terms. Actually, web services and WCF are just a tools. No matter how you will do it –it can be done with WCF, J2EE, PHP, WS-*, Borland Delphi or even pure C. Which technology you will use is a consideration of available developer workforce, learning curve of technology, scalability and your other needs. But I think it has to be done via some network, and that business layer should be located close to database.

I hope this covers also your last consideration about the need to do something with your five apps if DB changes. Look – the need to change DB should come from the business need. If you need to store some additional information – you will have redesign it’s business service, and probably add some fields to DB. You will have to alter database and your business service. There is a big chance you will not have to modify your five apps, if you design business layer smartly. For example, imagine you will want to calculate customer scoring and use it internally to calculate good offer for your best customers – in such case you will most probably add one field to DB and alter some services (add service to calculate scoring and alter offer calculation service) without any need to change GUI. However, if one day you will jump into conclusion that customer name and address is too little, and you want to gather all his educational, healthy, family, marriage, criminal and credit record – you will need not only to change DB, but to change business layer and provide users of your five applications with dozen of new input screens to collect such data…

Answer 2

Chances are that over time, each of the apps will try to pull the database in different directions. The safest thing to do would be to separate it up into several databases and then deal with data replication somehow. However, that has its own issues especially if all of the databases need to be instantly synced up instead of eventually synced up.

Here is an interesting read: http://devlicio.us/blogs/casey/archive/2009/05/14/commercial-suicide-integration-at-the-database-level.aspx

I have yet to hear an experienced DB tell me they prefer multiple databases however.

Answer 3

Isolate your data with

• Stored procedures in database to provide basic control over referential integrity

• Service-oriented middleware to
  provide control about BUSSINES usage of data

And provide some smart runtime version control, so the users of obsolete version of client app will be notified about the need to upgrade with nice message box instead of just error message.

Answer 4

Any problem can be solved by adding an extra layer of indirection (except the problem of too many layers)

Each app would talk to the database via it's own views / stored procedures as long as these still work then the app will be OK. (This could also be implemented as a DAL layer in code)

In this way if you need to make changes to the database there is only one place that changes need to be made.

Having said that, I would not implement this as a single database. Each app would have it's own data with it's own database.

If one app needs data from another app there are 3 ways of doing this:

• A service from the code to deliver the data
• Replicate the data over
• Linked tables

This would make it very clear which app owned which data.

Answer 5

Werner Vogel, the CTO of Amazon, says that in services should be isolated, independent, and own their own data in a service oriented architecture.

Maybe an alternative approach would be to expose the shared portions of the applications as services that own their data. Let applications that require that information get it from one place.

This arrangement would give you a single place to apply the rules that pertain to that data. Change them in one place, and all clients see them at once.

One issue I'd have with several apps sharing the data would be the possibility of apps applying different business rules to the same data.

Maybe you can hide schema details with a combination of views and stored procedures. This would keep the common data layer in the database, where it'd be easier to maintain in a single spot for all apps. The downside is that you'd be tied to a single database with its stored proc language, but that's not too severe. Most places don't switch databases lightly. Middle tier code comes and goes, but data lives on.

Answer 6

Your concern touches on what the main goal should be in designing this system. If you need to provide several clients access to one database, then there needs to be some type of shared data layer.

You don't specify if this is a web application or installable client, but a similar approach works for both. In the former case, the MVC pattern will do what you want. You just have to think about your applications as sets of views and controls that use the same models. For separate client apps, the same approach works, but you'll also want to consider using a web service or some other rpc technique to get at the data. This way, there's still a common data layer managing access.

Answer 7

That really depends on what you mean by "change the database". If your applications can change the database at runtime (for example, by allowing customers to define custom entities / attributes and creating / altering tables to store those attributes) you just need to "namespace" all the custom (e.g. alterable at runtime) database objects per application. But that's something you'd have to do anyway in order to provide clear upgrade path.

If, OTOH, you mean "version 2 of application #5 is now being developed and it needs new database structure that breaks applications #1 thru #4 now in production" the answer is "you can't - and shouldn't do it". Your database changes should be purely incremental. It probably makes sense to have a common "API" layer shared across all applications that provides common functionality that they all need from the database and use API versioning on that.