I'm looking at creating a "basic" web-application based system that can operate on both an intranet and an extranet. As some data is "restricted", it'll be placed on the intranet, and the rest will be placed on the extranet.
So, there will be two separate databases, with minimal duplication between them (hopefully!).
i.e. consider company staff details - some fields, such as name, and job description, can be stored in the database on the extranet server. Salary instead should be stored in the database on the intranet server (as the intranet server is hopefully going to be more secure than the extranet server).
Now, the trouble is :- how do I access all the data "seamlessly" through a standard front-end?
Ideally, I'd like to be able to use just one front-end application, and it would somehow know where you're currently logged in from (i.e. on the intranet, or from the internet - extranet), and be able to retrieve the relative data.
So if I was to log in from the intranet, it could pull out all the details from both databases (name, job description & salary), and if I was to log in via the internet, I could only get the name and job description.
Realistically, I might need to put the front end on BOTH the intranet & extranet servers - as technically, although the intranet server can access the extranet server via the DMZ on the firewall, the extranet server can't access the intranet server.
I'm *thinking* of perhaps creating an API (probably some kind of CGI script or the like, residing on the server), and the front-end would send all calls to the API. The API would then query the relevant databases, and either return valid data, or error messages if you're trying to access the wrong data (i.e. private information via the extranet).
Saves people having to "hard code" what can & can't be accessed directly into the web front-end... only the API would need to be modified to support the entire system.
Would this kind of methodology work? Anyone have experience in this kind of thing?
I don't like the thought of having two completely separate front ends, designed to access each database individually - I don't want to have to replicate the databases, I'd rather have it all work "in real-time", and be as seamless as possible...
A big ask, but hopefully someone can give me a bit of advice!