Skip to main content

[OT] tree structure design question

2 replies [Last post]
bolsover
Offline
Joined: 2005-01-10
Points: 0

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.

db

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Richard Bair

>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?

I'm not totally familiar with MySql, but the latest version supports
sub-queries, correct? If this is the case, then creating another table
called "parents" or something would be relatively straightforward, right?
so:

create table regionsorwhatnot (
regionId integer not null primary key,
somedata varchar(255)
);

create table relationships (
parentRegionId integer not null,
childRegionId integer not null,
foreign key (parentRegionId) references regionsorwhatnot(regionId),
foreign key (parentRegionId) references regionsorwhatnot(regionId),
primary key(parentRegionId, childRegionId)
);

so given a region like "North America" with an id of 1001 I could get all of
its child nodes by:

select r.* from regionsorwhatnot r, relationships where regionid =
childRegionId and parentRegionId = 1001

Not sure if that's what you are asking, but that should give you all of the
child nodes for any given parent node. Also, a single node could have more
than one parent in this scenario.

As for data access, binding, etc --> were you interested in that part of the
story too? For an application that needed to be written today, I'd do it the
old-fashioned way (listener on tree node expansion, execute a new request)
except I'd go one further and with each node expansion I'd get all of the
direct sub nodes, AND all of the next level nodes so that the user
experiences less delay when loading.

For the application of tomorrow, I'd pump all of the data from these queries
into DataTables in a DataSet, bind the JTree (or JTreeTable) to the DataSet,
and be happy. You might still need some node expansion listeners to know
what data to load, but you should be able to manage loading the DataTable's
easily enough.

The only thing missing from JDNC to allow you to do this right now is the
Tree binding problem. That hasn't been solved yet.

Personally I'd also transport the data using some form of XML and a
webserver, such as WebRowSetXML or using the DataSet XML over XML-RPC or
some such mechanism. That way you don't have an open database connection
over the internet/intranet. Maybe its not a problem on an intranet, and you
can just connect straight to the database.

Richard

---------------------------------------------------------------------
To unsubscribe, e-mail: jdnc-unsubscribe@jdnc.dev.java.net
For additional commands, e-mail: jdnc-help@jdnc.dev.java.net

bolsover
Offline
Joined: 2005-01-10
Points: 0

Richard

Thanks fot the input - very useful.

Yes - later versions (4.1 and above) of MySql do support sub-queries. I believe that V5.0 introduces rudimentary support for triggers while foreign key support will be in V5.1.

For now, I have to work with 4.1 and live with the shortcomings.

Data binding is certainly of interest; the existing app implements a listener on tree node expansion and retrieves all the direct sub node data. I stopped short of retrieving all the next level also because the volume of data made the system somewhat unresponsive.

I would be interested in seeing the JDNC solution for tree data binding - is that likely any time soon?

I would also like to know more about the asynchronous data loading features proposed.

I also like your suggestion for data transport - the existing app does connect directly to the database - even over the internet. It works - but I don't like it much. However, this is a single-handed development and my client budget is very limited!

db