I searched on the web how to access in an efficient manner to a central database at a remote location and I met suggestions to use web services instead direct access (i.e. JDBC etc ) to a database.I wonder the reason of that and any other suggestions.
Web Services vs Direct Database Access for Android Apps
androiddatabaseweb services
Related Solutions
OpenAM.
http://forgerock.com/openam.html
How should user details and access rights be stored? (data model, location, format)
Identity Manager. Separate from any web server. LDAP-based.
What are good design patterns for representing and tracking these in the server? (sessions in memory, db lookups each time, etc)
Solved by your framework. Think no more. Just use your framework's already-built good design patterns.
What are good patterns for mapping these rights to the services in a secure way in the code base?
Solved by your framework. Each framework uses a slightly different approach. Each language has slightly different features. Django, for example, uses Python's decorators heavily for this.
What architectural choices can help keep the system more secure and reliable?
More? More than what?
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
Adding a web service layer gives you an opportunity to make your client more lightweight, both in terms of the required CPU power and the bandwidth used during the processing. Both factors are extremely important to end-users:
By introducing a web application layer you move the bulk of the processing from a hand-held mobile low-power, low bandwidth, low-memory client to a plugged-in, high-power high-bandwidth, server that has more memory than it needs - an environment where processing and communications cost a fraction of what they cost on a client.
But wait, there is something in it for you as well: by splitting the system you get more control over your business rules, the structure of your database, and the versions of what's out there. Once you let a mobile client connect directly to the database, your design is "married" to that database structure: almost any change would break backward compatibility to a client that may be reluctant to upgrade his app.
In contrast, adding a web service in between lets you evolve the interface to mobile clients in more manageable ways: for example, you could keep the old interface in place, add a new one that works "in parallel" with it, and then entirely restructure your database without breaking a single client.
If you follow some pretty basic design principles while designing your web service, you could also get significant benefits by reusing mature server-side infrastructure that has been put in place: for example, you can get cache and proxy services for free.
Finally, this will open the door to other developers exposing your application to platforms that you could not service yourself, ultimately playing to your company's advantage.