[odb-users] one-to-many relationship with property(ies)

[Boris Kolpackov]

Hi Davide,

Davide Anastasia <Davide.Anastasia at qualitycapital.com> writes:

> I'm trying to build an ODB layer over an already existing database. I
> have a one-to-many relationship between table A and table B, with a
> middle table containing (primary_key(A), primary_key(B), int). In
> practice, I have a relationship with a weight between A and B. I'm not
> sure how I can model this.

I think having a composite value that contains the pointer plus the
extra data and then using a vector of those is the most natural way.
Here is an example:

#pragma db object
class employee
{
public:
  #pragma db id auto
  unsigned long id_;
};

#pragma db value
struct employee_info
{
  #pragma db not_null
  employee* ptr;
  int data;
};

#pragma db object
class employer
{
public:
  #pragma db id auto
  unsigned long id_;

  #pragma db unordered
  std::vector<employee_info> employees;
};

With this model the auto-generated schema (for PostgreSQL) looks along
these lines:

CREATE TABLE employee (
  id BIGSERIAL NOT NULL PRIMARY KEY);

CREATE TABLE employer (
  id BIGSERIAL NOT NULL PRIMARY KEY);

CREATE TABLE employer_employees (
  object_id BIGINT NOT NULL,
  value_ptr BIGINT NOT NULL,
  value_data INTEGER NOT NULL);

As a general tip, when trying to use an existing schema, it can be
helpful to auto-generate a "test schema" from the object model. Once
they are similar enough, then you know you have achieved the correct
mapping.

Boris