There are different ways to do it but in general you have 2 choices:
Rough imports: overwrite what you have with the new data coming in. So a full new import of the data.
or the alternative:
Track changes:
Don't just use a modified date field but really track all changes. Then let the changes be downloadable with a from date. So, the client asks for all changes since the last time it asked. Then it will apply all changes and you can handle the arising conflicts.
Long story short: all in one.
If you consider one of these as "master" and the other as an augmented replica, that updates are replicated one way only, and you can survive large latencies in synchronisation (and I'll let you define "large") then two separate DBs will work. For anything else I'd suggest a single DB.
If you have two-way updates will they be synchronous or async? The former and you have closely coupled the two DBs. The latter and you must have conflict resolution measures in place, adding complexity. With a single DB you have the whole weath of concurrency technology to support you.
With any copy process there will be latency, even if it is only milli- or micro-seconds. That's all it takes to introduce inconsistency between the source's and replica's data, however, and then you have the whole reconciliation & resolution thing again.
How will you handle DR? How will you ensure a common sync point across two DBs, potentially on different servers (or data centres)? How do you ensure you get the two matching tapes back from the off-site vault first time, every time?
Any schema change which adds tables or columns will be transparent to the other application. (You're not writing SELECT *
, are you? ARE YOU?) Removing objects should only affect modules which use then, which you'd want to change anyway. (Two DBs with a redundant table in one but removed from the other smells like a potential source of great confusion to me.) Changing schema because business rules have changed may cause some duplication of work; but it seems to me if one application isn't implementing that business rule it had no call to be in that part of the DB anyway.
Could you store the ORM-implementing code files in way that made them common to both applications? Then schema changes will affect only one set of code.
"Database as single point of failure." Well, fair point. But then it has been on absolutely every application I've ever worked on since they've all had a DB. That's why we have cluster/ mirror/ high-availability built into the DBMS products. If you have two DBs and one fails, what then? Back to the reconciliation/ conflict resolution cycle. It is also a single point of tuning; fix it one place and it's fixed everywhere!
I would have thought views, stored procedures, the ORM itself, and limiting the items in any SELECT query would have been able to isolate any part of the application from data in which it has no interest.
My reading of your question is that your two applications are actually quite closely coupled. My response is coloured by that. I've written plenty of systems where we bulk copy or two-way replicate data and deal with the consequences.
Best Answer
Plenty of sensitive information gets stored in databases. In fact, a central database is probably the most secure way to store this data. Large enterprise databases have tons of functionality to do things like encrypt sensitive information, to audit who accesses it, to limit or prevent people including DBAs from viewing the data, etc. You can have professional security experts monitoring the environment and professional DBAs overseeing backups so that you don't lose data. It would almost certainly be much easier to compromise data stored on some random user's mobile device or laptop than to penetrate a well designed security infrastructure and compromise a proper central database.
You could design the system with a central database that stores only encrypted data and store the user's private key on the user's device. That way even if the central database is completely compromised, the data is usable only by the user. Of course, that means that you can't restore the user's data if they lose their key (say the only copy was on their phone and their phone was damaged). And if someone compromises the key and, presumably, their login credentials, they would be able to see the data.