I am working on a legacy system, but I have a fair amount of flexibility to make the changes I feel are necesary. One of the projects that I am working on is converting the code base from a mix of EJB and JDBC to (mostly) hibernate. Things have been going very smooth except for one complex mapping that I'm not sure how to resolve.
The system I am working on has been designed to be highly customizable. We have one Facility table that consists of the facility contracts. Each facility is associated with exactly 1 network. So far, so good. The problem: Many of the network details are customizable per-user. With the current system, we are using *HUGE* hand coded SQL statements to handle all of the customization. The queries that we use are typically around 30 lines long, do an inner join on 4 tables (including joining a table to itself), 4 left joins and a slew of COALESCE() calls. Mapping that whole mess seems like a non-starter. Even if I could do it, I suspect that the performance hit would be significant.
My solution was to create a virtual view (since MySQL doesn't support real views and I expect to get better performance from a real table anyhow). The "view" will be rebuilt nightly or as needed. It will basically reduce the complex set of joins that we currently use down to a single table that is keyed off of User and NetworkId - reducing most of our queries down to a single inner join.
And now for the problem...
If I proceed with this plan, the database will look something like (excuse the DDL errors):
Code:
CREATE TABLE Users (
Id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
User VARCHAR(20) NOT NULL,
....
);
Code:
CREATE TABLE Facility (
Id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
NetworkId INTEGER NOT NULL,
Name VARCHAR(40) NOT NULL,
....
);
Code:
CREATE TABLE UserNetView (
UserId INTEGER NOT NULL,
NetworkId INTEGER NOT NULL,
... (options) ...
);
PRIMARY KEY (UserId, NetworkId);
This design will reduce the mess I am currently using to a single very ugly query that is run nightly (to update the "view") and then queries like:
Code:
SELECT {}
FROM
Facility JOIN UserNetView ON (Facility.NetworkId = UserNetView.NetworkId AND UserNetView.UserId = ?)
WHERE
...
in our functional code.
What I would like to create is a Facility object with a getNetwork() method that returns the network settings for the current user. I was hoping to just avoid using Session.load() and instead creating a series of custom queries like:
Code:
from Facility f where f.network.userId = :userId and f.id = :primkey
from Facility f where f.network.userId = :userId and f.name = :name
...etc...
I am not sure how to proceed with the mapping though. From a pure database point of view the relation between Facility and UserNetView is technically many-to-many I guess. But, if you include the User id - which I would always use - it is logically one-to-many (each facility will be associated with exactly one network entry per-user). At least, that is how I would prefer to treat the relation.
My best idea so far has been to make the relation many-to-many and then provide access functions in the Facility object to pull the network info from the Set.
One thing in my favor is that the Facility and UserNetView tables will both be read-only.
Is there a better way of handling this? Any suggestions are welcome.
Thanks,
Josh
Login
FAQ
Search
