List of articles Terminology Choose language

Kinds of determination

Nog

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
);

Determination 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

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

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

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" must exist in output
.a.(b#ref1 | c#ref1).  -- either "b" or "c" must exist in output

... 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+ref1.(b   c).  -- both "b"  and "c" must exist in output
.a#ref1,ref1.(b | c).  -- either "b" or "c" must exist in output

Continuation

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" must exist in output
.(b.d#ref1 | c.d#ref3).  -- either "b" or "c" must exist in output

... 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" must exist in output
.(b#ref1 | c#ref3).d.  -- either "b" or "c" must exist in output

P.S.

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

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

a.b#b1*b2*b3.c

Dmitry Turin

List of articles Terminology Choose language

Сайт управляется системой uCoz