Foreign key relations to only one part of a composite primary key with restriction to subsets

2 posts, 0 answers
  1. Maurizio
    Maurizio avatar
    43 posts
    Member since:
    Jan 2012

    Posted 04 May 2012 Link to this post


    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
  2. Zoran
    Zoran avatar
    534 posts

    Posted 08 May 2012 Link to this post

    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!
  3. DevCraft banner
Back to Top