This question is locked. New answers and comments are not allowed.
Hello,
my colleges and I have a difficult problem, which is also tricky to explain, I hope one of you can give us a hint how to solve it.
We are developing a software which simulates water distribution networks. These networks have pipe objects which have a 1 : N association to a list of demand objects.
Each network has a base version which contain all pipes and demands. Beside the base version a network may have other version, for example a version where some of the pipes are out of order, or some demands are different from the base version. Instead of copying the entire network for these (delta)version, we want to save only the pipes and demands which are different from the base version.
The problem is how to save the network with the different versions to the database when using OpenAccess and how to get the associations right. We thought of using composite identities, so each object has an id and a Version id like in the table below
Table Pipes:
PipeID | VersionID | Length(m) | Dimension (cm) | State
1 | 0 | 60 | 100 | Open
2 | 0 | 40 | 125 | Open
2 | 1 | 40 | 125 | Closed
Table Demands:
DemandID | VersionID | Demand (m³) | PipeID
1 | 0 | 10 | 1
2 | 0 | 15 | 2
2 | 1 | 12 | 2
At runtime we know which version is selected so we have to make sure that if there is (like in the table Demands) a demand with the same ID but different VersionID (like Demand 2 in table Demands) only the demand with the current version (VersionID 1) is loaded. Otherwise if there is no version object the base object should be loaded.
As at runtime it is known, only one object with the same id is loaded, it is sufficient to map the demands via the column PipeID to the corresponding Pipe object.This way it is working today with handwritten sql statements and joins. However we did not succeed in mapping this association with OpenAccess (Fluent Mapping) or any other ORM as the PipeId is only the first part of the composite primary Key (ID, VersionID). Defining the association with both components of the primary Key (PipeID + PipeVersionID) is no solution either, as in this case we would need for each version of a pipe a corresponding demand entry with the same version. But we want to allow for combinations Demand in Version 0 points to Pipe in Version 1 or vice versa. Is there any possibility to define foreign key relations to only one part of a composite primary key and to restrict the join to a subset of objects (currently loaded version)? Or do you have a better / completly different idea how to model versioned objects with relations to each other?
best regards
my colleges and I have a difficult problem, which is also tricky to explain, I hope one of you can give us a hint how to solve it.
We are developing a software which simulates water distribution networks. These networks have pipe objects which have a 1 : N association to a list of demand objects.
Each network has a base version which contain all pipes and demands. Beside the base version a network may have other version, for example a version where some of the pipes are out of order, or some demands are different from the base version. Instead of copying the entire network for these (delta)version, we want to save only the pipes and demands which are different from the base version.
The problem is how to save the network with the different versions to the database when using OpenAccess and how to get the associations right. We thought of using composite identities, so each object has an id and a Version id like in the table below
Table Pipes:
PipeID | VersionID | Length(m) | Dimension (cm) | State
1 | 0 | 60 | 100 | Open
2 | 0 | 40 | 125 | Open
2 | 1 | 40 | 125 | Closed
Table Demands:
DemandID | VersionID | Demand (m³) | PipeID
1 | 0 | 10 | 1
2 | 0 | 15 | 2
2 | 1 | 12 | 2
At runtime we know which version is selected so we have to make sure that if there is (like in the table Demands) a demand with the same ID but different VersionID (like Demand 2 in table Demands) only the demand with the current version (VersionID 1) is loaded. Otherwise if there is no version object the base object should be loaded.
As at runtime it is known, only one object with the same id is loaded, it is sufficient to map the demands via the column PipeID to the corresponding Pipe object.This way it is working today with handwritten sql statements and joins. However we did not succeed in mapping this association with OpenAccess (Fluent Mapping) or any other ORM as the PipeId is only the first part of the composite primary Key (ID, VersionID). Defining the association with both components of the primary Key (PipeID + PipeVersionID) is no solution either, as in this case we would need for each version of a pipe a corresponding demand entry with the same version. But we want to allow for combinations Demand in Version 0 points to Pipe in Version 1 or vice versa. Is there any possibility to define foreign key relations to only one part of a composite primary key and to restrict the join to a subset of objects (currently loaded version)? Or do you have a better / completly different idea how to model versioned objects with relations to each other?
best regards