[odb-users] Relational tables with an association table

[Adam Walters]

Hi Boris,

OK, with again some code removed for simplicity (and class names abbreviated for confidentiality), here are the classes involved:



First, the PC class. This has a one to many relationship with the CT class



#pragma db object pointer(std::unique_ptr)



class PC

{

public:

...

private:

                PC() {}

                friend class odb::access;



                int _cId;

                std::unique_ptr<CD>   _cdId; // CD class definition omitted for clarity



                std::vector< std::unique_ptr<CT> > _ct;



};



#pragma db member(PC::_cId) id auto




Now the CT class definition. Note that this has attributes that we need to access.

#pragma db object pointer(std::unique_ptr)

class CT
{
public:

...
private:
        CT(){}
        friend class odb::access;

        int _ctId;
        int _cId; // owning record PK

        std::string             _description;
        std::string             _shortName;
        bool                    _locked;
        bool                    _active;
        std::string             _transformMatrix;

        std::unique_ptr<T> _tId;
};

#pragma db member(CT::_ctId) id auto


Now the T class definition. This has a one-to-many relationship with the CT object, but we only need to get to it from the CT class, hence the single pointer in that class.

#pragma db object pointer(std::unique_ptr)

class T
{
public:

...
private:
        T(){}
        friend class odb::access;

        int                             _tId;
        int                             _manufacturerId;

        #pragma db set(setDescription)
        std::string             _description;
        std::string             _model;
        float                   _length;
        float                   _diameter;
        std::string             _graphic;
        std::string             _matrix;
};

#pragma db member(T::_tId) id auto


So to summarize, CT is an association table that links PC records to T records. For every PC record, there can be zero or more CT records with the same key. For every T record, there can be zero or more CT records with the same key. If I load a PC object using a specified key, I would expect to see a (possibly empty) list of CT records in it. Within each CT record in this list, I would also expect to see a corresponding T record. If I compile the PC class with the ODB compiler, it generates a sql file for the PC table definition. As part of this file, it creates the sql for a table called PC_CT which contains an id and a value field.

What I am having a hard time understanding is why is this additional table required as it appears to be used to perform the join between the PC and the CT tables? In my case, I presume the id field would contain the id of a PC record and the value field would contain the same value, which seems a bit redundant since that relationship is already defined between the two tables. If I have to create a separate table for each 1-to-many relationship i define, does the ODB insert mechanism populate these for me automatically? We are still at the stage of creating the ODB classes and reading manually populated data so we haven't got as far as doing inserts yet.


Any clarification would be much appreciated.


Adam Walters