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