List of articles   Choose language


Compositions of refinement

Types of compositions

Nog is two foreign keys, connecting three tables: refering fields are in one intermediate table.

create table a (
  id   num      primary key,
  data float
);
create table b (
  id   num      primary key,
  ref1 num      references a(id),   -- important
  ref2 num      references a(id),   -- ballast
  ref3 num      references c(id),   -- important
  ref4 num      references c(id),   -- ballast
  data float
);
create table c (
  id   num      primary key,
  data float
);
Refinement consist of two fields, listed through colon: field, refering to previous section ("a"), is specified first; field, refering to next section ("c"), is specified second (refering fields can have identical name). Syntax of nog consist of three section and looks so:
.a.b#ref1:ref3.c.

Buckle is two foreign keys, connecting three tables: refering fields refer to one intermediate table.

create table a (
  id   num      primary key,
  ref1 num      references b(id),   -- important
  ref2 num      references b(id),   -- ballast
  data float
);
create table b (
  id   num      primary key,
  data float
);
create table c (
  id   num      primary key,
  ref1 num      references b(id),   -- important
  ref2 num      references b(id),   -- ballast
  data float
);
Syntax of buckle consist of three section and looks so:
.a#ref1.b.c#ref3.

Crossroad is two foreign keys, connecting two tables in opposite directions: one refering field is in one table and refers to primary key of second table, other refering field is in second table and refers to primary key of first table.

create table a (
  id   num      primary key,
  ref  num      references b(id),
  data float
);
create table b (
  id   num      primary key,
  lnk  num      references a(id),
  data float
);
There exist two not equivalent syntax of crossroad (each consists of two sections).
.a#ref.b.

.a.b#lnk.

Branching is two foreign keys, connecting three tables: refering fields refer to one surrounding table

create table a (
  id   num      primary key,
  data float
);
create table b (
  id   num      primary key,
  ref1 num      references a(id),   -- important
  ref2 num      references a(id),   -- ballast
  data float
);
create table c (
  id   num      primary key,
  ref1 num      references a(id),   -- important
  ref2 num      references a(id),   -- ballast
  data float
);
(syntax of branching:)
.a.(b#ref1   c#ref1).  -- both "b"  and "c" are required
.a.(b#ref1 | c#ref1).  -- "b", or "c", or both are required
... or refering fields are in one surrounding table
create table a (
  id   num      primary key,
  ref1 num      references b(id),   -- important
  ref2 num      references b(id),   -- ballast
  ref3 num      references c(id),   -- important
  ref4 num      references c(id),   -- ballast
  data float
);
create table b (
  id   num      primary key,
  data float
);
create table c (
  id   num      primary key,
  data float
);
(syntax of branching:)
.a#ref1+ref3.(b   c).  -- both "b"  and "c" are required
.a#ref1^ref3.(b | c).  -- "b", or "c", or both are required

Continuation is two foreign keys, connecting three tables: refering fields refer to two surrounding tables

create table b (
  id   num      primary key,
  data float
);
create table c (
  id   num      primary key,
  data float
);
create table d (
  id   num      primary key,
  ref1 num      references b(id),   -- important
  ref2 num      references b(id),   -- ballast
  ref3 num      references c(id),   -- important
  ref4 num      references c(id),   -- ballast
  data float
);
(syntax of continuation:)
.(b.d#ref1   c.d#ref3).  -- both "b"  and "c" are required
.(b.d#ref1 | c.d#ref3).  -- "b", or "c", or both are required
... or refering fields are in two surrounding table
create table b (
  id   num      primary key,
  ref1 num      references d(id),   -- important
  ref2 num      references d(id),   -- ballast
  data float
);
create table c (
  id   num      primary key,
  ref1 num      references d(id),   -- important
  ref2 num      references d(id),   -- ballast
  data float
);
create table d (
  id   num      primary key,
  data float
);
(syntax of continuation:)
.(b#ref1   c#ref3).d.  -- both "b"  and "c" are required
.(b#ref1 | c#ref3).d.  -- "b", or "c", or both are required

Combined key

Если внешний ключ состоит из нескольких полей, то в детерминации ссылающиеся поля перечисляются через знак умножения.

If foreign key consist of several fields, then these fields is listed in refinement through sign of multiplication.

a.b#b1*b2*b3.c

P.S.

Article is guide to implement ideas of p.39-48 of pdf-document.



Dima Turin, dmitryturin@yandex.ru



List of articles   Choose language