How should I handle a shared database that needs to be referenced by multiple applications?

In my SOA, I have 2 services - a users service and products service. Both users and products can be "tagged" with 2 objects - countries and industries. This means that both services will have a join table and future services will need that as well. I would like the database of countries and industries to be standard and managed from one place if possible. There are a few options I can think of:

1. Keep the countries, industries, and other shared databases on it's own server and allow external read only connections while manipulating the data would have to be done by one app whose sole purpose is to manage that data.
2. Keep copies of those tables in a database local to the service, and have them act a开发者_如何学Gos slave tables. The master tables will be maintained by an app that manages that data and pushes out updates to those slave tables.

Am I missing any good options? Out of those 2 or any other proposed, which would you go with and why?

If you worry too much for referential integrity of your User and Product tables, I would keep a read only copy of those tables in multiple databases and establish FKs. Then write a single service to keep all the copies up to date.

Otherwise, hub-spoke solution you mentioned in option 1 would do the job. But you need to control data entry to your user and product tables programmatically to establish good data quality.

You might want to look into Replication. From your description, Snapshot replication would seem the most suitable. You would have one database where you maintain your common tables (using a dedicated app, or direct SQL, or whatever), and then your service databases would be subscribers. SQL Server would take care of copying the data around between the servers.

You can even then have Foreign Keys to the common tables (because they appear in each of the service databases)

Which is effectively your option 2, but with the "how to synchronize the master and slave tables" bit filled in.

Use one database with all the tables, then open a connection from both services to it, write clean transactions and let ACID properties handle any (unlikely) problems?

Simple, efficient, easy to do.

Is there a requirement that would prevent this solution from working?

Replication and data caching is one of those things that is really interesting to talk about but rarely the right choice. Databases are designed to deal with shared data and most are quite good at it. You should only keep multiple copies of the data if it addresses a specific requirement such as:

• Service instances deployed to multiple locations with poor or unreliable connectivity between the sites.
• Performance under very high loads (this can usually be addressed by simply buying the right hardware).

If you don’t have a special requirement, listen to Kdansky. Keep it simple. Spend as much of your development time providing your users with features... not writing data integrity monitors.

Right now you are basically comparing simplicity versus performance (since you mentioned referential integrity isn't an issue). In option 1 everything is centrally located and managed, it will require additional network hops to pull the data but you don't have to worry about stale data or managing multiple databases which could have their own issues. If your main server works then it works for everyone. In option 2 storing your data locally will improve your performance but it will complicate long term maintenance of the system and create potential data inconsistency problems (although given your data is countries and industries it probably won't change very often so it shouldn't need to be updated frequently).

My recommendation would be to build the simpler solution (option 1) and then if the need arises tune for performance (using option 2 or possibly some other solution). When tuning for performance take a look at the latency between the servers before you add local databases, you may be able to fix the problem with indices or statistics.