I’m hoping for some advice about modifying a solution so that the data can be shared by different organisations.
The situation is that the charity I work for has a mature and stable solution (remotely hosted, FM14). It basically consists of a People table, with an Activities table, plus tables for Attendances, Outcomes, etc.
There’s a new initiative, which involves 3 other charities working in partnership with mine. They will all be doing similar work, with shared outcomes but separate targets, so we’ll need to know that collectively the project has achieved [n] outcomes, and that Charity A has reached their target of [n], Charity B has yet to achieve [n], etc.
They are unlikely to be working with the same people, but there may be some overlap – e.g. a person may use Charity A, achieve one outcome, then move on to Charity B and achieve another. So obviously we want to avoid duplicating people. None of the actual activities offered by each charity are shared.
So I’m trying to figure out how best to structure the data. It seems that I should have just one People table, but I don’t know whether to have separate Activities tables for each organisation, and, if not, how to distinguish one organisation’s activity and achievement from another’s. Or should I have a completely separate installation for each organisation, with sharing achieved via importing from the various organisations’ data?
If you have any experience of such a setup, or any advice, I’d be very grateful for your input.