skip to main content

Model/Database mapping

We illustrate how the relationships and constraints expressed in the model are rendered within the generated database.

When we build a model via the Designer and build it on a Cloudlet, Livebase generates the corresponding database based on some rules:

  • One table is generated for each class;
  • One-to-many or many-to-one associations are rendered with a foreign key in the “many-to-many” table, pointing to the “one-to-one” table;
  • one-to-one associations are rendered with a foreign key in either table, depending on the navigability of the association;
  • Many-to-many associations are rendered with an additional table, referred to as a relationship table. It has two foreign keys to the tables involved, and its records are the identifiers of the instances between which the association exists;
  • compositions are rendered with a foreign key in the table of the part class, to that of the whole class;
  • Minimum and maximum cardinality constraints cannot be expressed in the database: they are verified only at the application level;
  • In general, the navigability of an association can influence whether and how it can be translated in the database.

Let’s make this clearer with a series of examples. In all of them we will assume two classes ClassA and ClassB with roles RoleA and RoleB respectively, connected by associations of various types. In the images accompanying the above examples, we will indicate the columns on which a not null constraint is defined in bold, and those for which a unique constraint is defined in cursive. Finally, in the expressions of the minimum and maximum cardinality constraints we will make use of the variables n and N to indicate arbitrary values, with N>n>0.

One-to-many/many-to-one associations #

If the cardinality of RoleA is 01 and that of RoleB is 0N (* in Designer), regardless of navigability…

Association 01 to 0N all

…in the ClassB table, the foreign key to ClassA will always be present.

Association 01 to 0N all mapped

If the cardinality of RoleA is 1 and that of RoleB is 0N (* in Designer), and the association is navigable from ClassB to ClassA or both ways…

Association 1 to 0N from target

…table ClassB has the foreign key to ClassA, with the not null constraint.

Association 1 to 0N from target mapped

If the cardinality of RoleA is 01 and that of RoleB is nN (C in Designer), and the association is navigable from ClassA to ClassB or both ways…

Association 01 to nN from source

…table ClassB has the foreign key to ClassA.

Association 01 to nN from source mapped

One-to-many/many-to-one : modeling not allowed #

The following associations are not modelable because the navigability conditions do not allow the creation of instances of ClassA, since RoleB is not accessible:

  • associations where the cardinality of RoleA is 01 and that of RoleB is nN (C in the Designer), navigable from ClassB to ClassA.

The following associations are not modelable because the navigability conditions do not allow instances of ClassB to be created, since RoleA is not accessible:

  • associations where the cardinality of RoleA is 1 and that of RoleB is 0N (* in Designer), navigable from ClassA to ClassB or both ways.

The following associations are not modelable due to the egg-chicken problem between ClassA and ClassB. It is not possible to define an instance of one in the absence of at least one instance of the other:

  • associations where the cardinality of RoleA is 1 and that of RoleB is nN (C in Designer), regardless of navigability.

One-to-one associations #

If the cardinality of RoleA is 01 and that of RoleB is 01, and the association is navigable from ClassA to ClassB or both…

Association 01 to 01 from source

…table ClassA has the foreign key to ClassB, with a uniqueness constraint defined.

Association 01 to 01 from source mapped

If the association is only navigable from ClassB to ClassA

Association 01 to 01 from target

…it will be ClassB that has the foreign key to ClassA, with the unique constraint defined.

Association 01 to 01 from target mapped

If the cardinality of RoleA is 01 and that of RoleB is 1, and the association is navigable from ClassA to ClassB or both…

Association 01 to 1 from source

…table ClassA has the foreign key to ClassB, with not null and uniqueness constraints defined.

Association 01 to 1 from source mapped

One-to-one: modeling not allowed #

The following associations are not modelable because the navigability conditions do not allow the creation of instances of ClassA, since RoleB is not accessible:

  • associations where the cardinality of RoleA is 01 and that of RoleB is 1, navigable from ClassB to ClassA.

The following associations are not modelable due to the egg-chicken problem between ClassA and ClassB: it is not possible to define an instance of one in the absence of at least one instance of the other:

  • associations where the cardinality of RoleA is 1 and that of RoleB is 1, regardless of navigability.

Many-to-many associations #

If the cardinality of RoleA is 0N (* in Designer) and that of RoleB is 0N (* in Designer), regardless of navigability…

Association 0N to 0N all

…a relation table named ClassA_RoleB is generated, which has the foreign key to ClassA and ClassB. The respective columns are not null and a unique constraint is defined on the combination of both.

Association 0N to 0N all mapped

If the cardinality of RoleA is 0N (* in Designer) and that of RoleB is nN (C in Designer), and the association is navigable from ClassA to ClassB or both…

Association 0N to nN from source

…a relation table named ClassA_RoleB is generated, which has the foreign key to ClassA and ClassB. The respective columns are not null and a uniqueness constraint is defined on the combination of both.

Association 0N to nN from source mapped

Many-to-many: modeling not allowed #

The following associations are not modelable because the navigability conditions do not allow the creation of instances of ClassA, since RoleB is not accessible:

  • associations where the cardinality of RoleA is 0N (* in Designer) and that of RoleB is nN (C in Designer), navigable from ClassB to ClassA.

The following associations are not modelable because of the egg-chicken problem between ClassA and ClassB: it is not possible to define an instance of one in the absence of at least one instance of the other:

  • associations where the cardinality of RoleA is nN (C in Designer) and that of RoleB is nN (C in Designer), regardless of navigability.

Compositions #

Compositions towards to-one parts…

Composition to one part

…are rendered with the foreign key in the part table to the whole table. The related column has a unique constraint.

Composition to one part mapped

Compositions towards to-many parts…

Composition to many part

…are rendered with the foreign key in the part table to the whole table, with no constraints defined on the related column.

Composition to many part mapped