This is a migrated thread and some comments may be shown as answers.
Foreign key relations to only one part of a composite primary key with restriction to subsets
1 Answer 37 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Top achievements
Rank 2
Maurizio asked on 04 May 2012, 01:43 PM

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

1 Answer, 1 is accepted

Sort by
Telerik team
answered on 08 May 2012, 04:01 PM
Hi Thomas,

 Unfortunately, I am afraid that there is no way to map your association scenario out-of-the-box with OpenAccess or any other general-purpose ORM product to my knowledge. The bottom line here is that you need a conditional statement to be executed on the database server when you try to load a referenced object. That statement should check if there is row with version equal to the current one otherwise return the object with version equal to 0. 

Here is how the association resolution works with OpenAccess: There is metadata telling us which is the foreign key column(s) that hold the information about the referenced row in the database. When you try to load a referenced object, OpenAccess checks if there is a row in the referenced table that has its Primary Key column with the value of your foreign key. If no such row is found NULL is returned(or exception is thrown depending on the configuration). In your case, you have composite primary key. If you map only the first part of the primary key(PipeId) in the association, you will always get the first row in the database that has PipeId(in pipes table) = PipeId (in Demands table) which means you will always get the Pipe object with Version=0.

Kind regards,

the Telerik team
Follow @OpenAccessORM Twitter channel to get first the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
General Discussions
Asked by
Top achievements
Rank 2
Answers by
Telerik team
Share this question