Admin Area Specification

This page outlines the proposed changes and flow for the admin area, used by InSync staff to manage client organisations, umbrellas, users, and organisation sectors.

Click below to add an umbrella organisation

An Umbrella organisation allows grouping of multiple client organisations/departments under a single administrative structure.

Add Umbrella Organisation

Click below to add a new organisation

Organisations are individual clients that may or may not belong to an umbrella. Selecting 'None' means the organisation is standalone.

Add Organisation

Click below to add a new user

Client Admin users have elevated privileges for their organisation. Umbrella Users can see and manage multiple organisations within their umbrella group.

Add User

Click below to add a new organisation sector

Sectors allow organisations to be grouped across umbrellas and can support filtering and reporting by sector.

Add Organisation Sector
Developer Notes:

Current Organisations

Organisation / Umbrella Organisation Umbrella Sector Client Link
CAB Group CAB Group Healthcare Client Area
CAB Gateboard CAB Group Healthcare Client Area
CAB Newcastle CAB Group Healthcare Client Area
CAB Sunderland CAB Group Healthcare Client Area
ACME Standalone Retail Client Area
Developer Notes:

Database Structure for Umbrella System

Overview: This umbrella system allows multiple organisations to be grouped under a parent umbrella. Organisations can either be standalone or linked to an umbrella via uolinkid. Organisations can also be linked to one or more sectors using a pivot table.

Schema Changes

-- Add uolinkid to organisations table
ALTER TABLE organisations
ADD COLUMN uolinkid INT DEFAULT NULL;

-- Create umbrellas table
CREATE TABLE umbrellas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    umbrella_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add umbrella user flag to users table
ALTER TABLE users
ADD COLUMN umbrella_user TINYINT(1) DEFAULT 0;

-- Create organisation sectors table
CREATE TABLE organisation_sectors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sector_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create organisation_sector_pivot table
CREATE TABLE organisation_sector_pivot (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organisation_id INT NOT NULL,
    sector_id INT NOT NULL,
    FOREIGN KEY (organisation_id) REFERENCES organisations(id),
    FOREIGN KEY (sector_id) REFERENCES organisation_sectors(id)
);

Example Queries

-- Fetch all organisations in the same umbrella
SELECT * FROM organisations
WHERE uolinkid = (SELECT uolinkid FROM organisations WHERE id = :current_organisation_id);

-- Fetch all sectors for a given organisation
SELECT os.sector_name
FROM organisation_sectors os
JOIN organisation_sector_pivot osp ON os.id = osp.sector_id
WHERE osp.organisation_id = :current_organisation_id;

-- Fetch all organisations in a specific sector
SELECT o.*
FROM organisations o
JOIN organisation_sector_pivot osp ON o.id = osp.organisation_id
WHERE osp.sector_id = :selected_sector_id;