[OT] tree structure design question
Design Question - this is not strictly a JDNC question but relates to the data structure underpinning an application and the mechanisms for accessing that data so I hope I will be forgiven.
First, an explanation of (some of) the issues:
The project has both GUI (Swing/JDNC) and Browser (Struts) interfaces and is accessible by both local intranet and through the internet. In the case of the Swing application, the delivery of relatively large volumes of data is not too much of a problem. However, in the case of the Struts application, large volumes of data are (in the present design) avoided by using a custom data provided and optimised SQL queries.
Part of the application is a tree structure that shows client organisations divided into national regions (North, South, East etc.).
Within each region the client contact organisations are detailed (Laboratory#1, Laboratory#2, Laboratory#3 etc.)
Each client organisation may be divided into several divisions (Thermal analysis, Chemical analysis etc.)
Regions, organisations and divisions may each have associated contact personnel (Regional administrator, Laboratory head, Divisional manager etc.)
Users of the system are restricted to viewing only that data associated with regions to which they have been granted access; some users can only view the data associated with one region whilst others can see all regions.
To date, managing the data structure has been relatively simple - other than the fact that regional boundaries sometimes shift with the resultant move of Laboratories and personnel.
The underlying data structure is a simple table containing all the data for Regions, Laboratories, Divisions and Personnel each child record having a link to its parent
My client in his wisdom has now decided that he wants to reorganise his sales force into Thermal and Chemical specialists and effectively duplicate the existing regions (Thermal North, Chemical North, Thermal South etc.) but retain the flexibility for regional boundaries to move.
The reorganisation now means that Laboratory#1 may be in both Thermal North and Chemical North regions rather than having a single unique parent as before.
Now the question
Given that I am constrained to using MySql as the underlying storage mechanism, what data retrieval and persistence mechanisms are suggested and how should the data be organised to permit the access restrictions indicated?
All sugestions, links to resources etc appreciated.