[odb-users] Relational tables with an association table

[Adam Walters]

Hi there
I have (in its simplest form) a three table relationship: table A, table B, and table C that is on the many side of a 1-many relationship with A and with B. Thus each record in 'C' references the primary key id for an 'A' record and that for a 'B' record, creating an association between the two. If I follow the to-many example in your user guide and create a class for A that contains a vector of shared_ptr<B>, the ODB compiler creates an association table automatically for me with an id and value column to manage the association. With further tweaking, I can even change the table and column names and types to suit.

The problem for me is that I want to access the records/objects for A, B, *and C* from a starting point of an A object. The reason for this is that I would like to add attributes to the 'C' records to allow me to create the same association with different attributes for a given A and B pair. For example, the same object from table B is associated multiple times with (placed in) a scene held in table A and each is transformed differently to alter its location in the scene. In this case, the transformation matrix for each association is held in the 'C' record since it is different for each association.

In this (somewhat simplified) example, I want to be able to load a given scene record from A and automatically load all of the 'C' association records into a vector within that 'A' object. Each 'C' object would then contain the single 'B' object that the association record references. If I try to create the 'C' objects as a vector in the 'A' object, the ODB compiler tries to create another intermediate table to link 'A' to 'C' which is obviously redundant.

I have looked at the samples you provide, but none of them seems to match this situation. What is the best way to do this, short of loading each set of A, B, and C separately and combining them myself?

Cheers,
Adam Walters
Navigate Surgical Technologies