1

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 as 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?

Tony
  • 18,776
  • 31
  • 129
  • 193

5 Answers5

1

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.

mevdiven
  • 1,902
  • 2
  • 17
  • 33
  • I guess referential integrity is not a huge concern in this situation, and I know it can be done either way. I'm more interested in understanding what the best practice is and just using that. – Tony Jan 14 '11 at 15:37
1

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Interesting, I will check that out. Are you saying the PKs would be the same in the common tables and the replicated tables, so I would essentially have FK references to the common table and no cross domain joins? – Tony Jan 14 '11 at 15:23
  • @Tony - that would be correct - SQL server will copy the table definitions and data across exactly. I'd recommend you try this out with a couple of toy databases first, just to get a feel for how it would work (in fact, I just did before answering this question, because I hadn't personally used Snapshot before, and I wanted to make sure the bit I said about foreign keys was right). – Damien_The_Unbeliever Jan 14 '11 at 15:25
  • Ok. I will play around but one more question - can I have the replicated tables in a database with tables that are not replicated? In other words I want there to be one local database. Some tables can be replicates of shared data in master tables whereas others will be normal local tables. – Tony Jan 14 '11 at 15:47
  • @Tony - yes, that's fine. You don't have to replicate all tables from the publication database, and the subscription databases can contain any additional tables you want. There are a lot of options for fine-tuning this further. – Damien_The_Unbeliever Jan 14 '11 at 15:51
0

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?

Kajetan Abt
  • 1,505
  • 3
  • 15
  • 28
  • Not a requirement but there would be a network hop to grab associated data right? Cross domain joins.... Also I'm not a database architect but by clean transactions and ACID properties do you mean lock the database when I write to it? Do you agree that a separate app to manage that data is probably best? – Tony Jan 14 '11 at 15:04
0

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.

ErnieL
  • 5,773
  • 1
  • 23
  • 27
0

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.

Braden Becker
  • 2,489
  • 20
  • 15