Recursive Arcs can be modeled choosing one of those strategy.

  • Use self-referencing keys
  • Use navigation table


 



Use self referencing keys

The recusion is modeled adding a foreign key referencing the same table.

 

   

-- ----------------------------------------------------------------
--          Indyco Builder - Auto-Generated DDL Script           --
-- ----------------------------------------------------------------
--
-- Selected Profile . . . . : Default
--  + Shared Hierarchies. . : Do not snowflake
--  + Cross Dimensional Attr: Bridge table without surrogates
--  + Degenerate Dimensions : Use dimension table
--  + Multiple Arcs . . . . : Bridge table without surrogates
--  + Recursions. . . . . . : Use self-referencing keys
--
-- Target DB. . . . . . . . : PostgreSql
--  + Export Primary Keys . : True
--  + Export Foreign Keys . : True
--
-- ----------------------------------------------------------------

CREATE TABLE "FT_ACTIVITY"
(
  "ID_EMPLOYEE" INTEGER,
  "HOURS_WORKED" NUMERIC (18,3)
);
ALTER TABLE "FT_ACTIVITY" ADD CONSTRAINT "PK_FT_ACTIVITY" PRIMARY KEY ("ID_EMPLOYEE");


CREATE TABLE "DT_EMPLOYEE"
(
  "ID_EMPLOYEE" INTEGER NOT NULL,
  "EMPLOYEE" VARCHAR (255),
  "ROLE" VARCHAR (255),
  "ID_EMPLOYEE_2" INTEGER
);
ALTER TABLE "DT_EMPLOYEE" ADD CONSTRAINT "PK_DT_EMPLOYEE" PRIMARY KEY ("ID_EMPLOYEE");


ALTER TABLE "FT_ACTIVITY" ADD CONSTRAINT "FK_FT_ACTV_ID_EMPL_ID_EMPL" FOREIGN KEY ("ID_EMPLOYEE") REFERENCES "DT_EMPLOYEE" ("ID_EMPLOYEE");
ALTER TABLE "DT_EMPLOYEE" ADD CONSTRAINT "FK_DT_EMPL_ID_EMPL_2_ID_EMPL" FOREIGN KEY ("ID_EMPLOYEE_2") REFERENCES "DT_EMPLOYEE" ("ID_EMPLOYEE"); 

   




Use navigation table

The recursion is modeled as a dedicated navigation table, containing parent / child relation, level and leaf informations.

 

   

-- ----------------------------------------------------------------
--          Indyco Builder - Auto-Generated DDL Script           --
-- ----------------------------------------------------------------
--
-- Selected Profile . . . . : Custom
--  + Shared Hierarchies. . : Do not snowflake
--  + Cross Dimensional Attr: Bridge table without surrogates
--  + Degenerate Dimensions : Use dimension table
--  + Multiple Arcs . . . . : Bridge table without surrogates
--  + Recursions. . . . . . : Use navigation table
--
-- Target DB. . . . . . . . : PostgreSql
--  + Export Primary Keys . : True
--  + Export Foreign Keys . : True
--
-- ----------------------------------------------------------------

CREATE TABLE "FT_ACTIVITY"
(
  "ID_EMPLOYEE" INTEGER,
  "HOURS_WORKED" NUMERIC (18,3)
);
ALTER TABLE "FT_ACTIVITY" ADD CONSTRAINT "PK_FT_ACTIVITY" PRIMARY KEY ("ID_EMPLOYEE");


CREATE TABLE "DT_EMPLOYEE"
(
  "ID_EMPLOYEE" INTEGER NOT NULL,
  "EMPLOYEE" VARCHAR (255),
  "ROLE" VARCHAR (255)
);
ALTER TABLE "DT_EMPLOYEE" ADD CONSTRAINT "PK_DT_EMPLOYEE" PRIMARY KEY ("ID_EMPLOYEE");


CREATE TABLE "NAV_EMPLOYEE"
(
  "ID_EMPLOYEE_CHILD" INTEGER NOT NULL,
  "ID_EMPLOYEE_PARENT" INTEGER NOT NULL,
  "LEVEL" INTEGER,
  "LEAF" INTEGER
);
ALTER TABLE "NAV_EMPLOYEE" ADD CONSTRAINT "PK_NAV_EMPLOYEE" PRIMARY KEY ("ID_EMPLOYEE_CHILD", "ID_EMPLOYEE_PARENT");



ALTER TABLE "FT_ACTIVITY" ADD CONSTRAINT "FK_FT_ACTV_ID_EMPL_ID_EMPL" FOREIGN KEY ("ID_EMPLOYEE") REFERENCES "DT_EMPLOYEE" ("ID_EMPLOYEE");
ALTER TABLE "NAV_EMPLOYEE" ADD CONSTRAINT "FK_NAV_EMPL_ID_EMPL_CHLD_ID_E" FOREIGN KEY ("ID_EMPLOYEE_CHILD") REFERENCES "DT_EMPLOYEE" ("ID_EMPLOYEE");
ALTER TABLE "NAV_EMPLOYEE" ADD CONSTRAINT "FK_NAV_EMPL_ID_EMPL_PRNT_ID_E" FOREIGN KEY ("ID_EMPLOYEE_PARENT") REFERENCES "DT_EMPLOYEE" ("ID_EMPLOYEE");