Generalize DB Schema and support relations more directly

[mlhaufe]

There is a fundamental issue here having to do with how relationships are represented, or rather how they are not. There is currently an isSilence property as well as a Silence requirement type.

To support the workbox, many requirements have a follows property that links to the ParsedRequirement that introduces it.
This property is duplicated across a number of subclasses of Requirement explicitly as a reference to ParsedRequirement as the ORM won't let this be handled in the abstract class even though it's supposed to be a generic relationship and not hardcoded to this subtype.

There is no Requirement table. So I can't query for all requirements directly.

The generated query for loading the workbox is massive:

SELECT "p0".*,
       "m1"."id"                      AS "m1__id",
       "m1"."name"                    AS "m1__name",
       "m1"."email"                   AS "m1__email",
       "m1"."creation_date"           AS "m1__creation_date",
       "m1"."last_login_date"         AS "m1__last_login_date",
       "m1"."is_system_admin"         AS "m1__is_system_admin",
       "a2"."id"                      AS "a2__id",
       "a2"."name"                    AS "a2__name",
       "a2"."statement"               AS "a2__statement",
       "a2"."solution_id"             AS "a2__solution_id",
       "a2"."last_modified"           AS "a2__last_modified",
       "a2"."modified_by_id"          AS "a2__modified_by_id",
       "a2"."is_silence"              AS "a2__is_silence",
       "a2"."follows_id"              AS "a2__follows_id",
       "c3"."id"                      AS "c3__id",
       "c3"."name"                    AS "c3__name",
       "c3"."statement"               AS "c3__statement",
       "c3"."solution_id"             AS "c3__solution_id",
       "c3"."last_modified"           AS "c3__last_modified",
       "c3"."modified_by_id"          AS "c3__modified_by_id",
       "c3"."is_silence"              AS "c3__is_silence",
       "c3"."category"                AS "c3__category",
       "c3"."follows_id"              AS "c3__follows_id",
       "e4"."id"                      AS "e4__id",
       "e4"."name"                    AS "e4__name",
       "e4"."statement"               AS "e4__statement",
       "e4"."solution_id"             AS "e4__solution_id",
       "e4"."last_modified"           AS "e4__last_modified",
       "e4"."modified_by_id"          AS "e4__modified_by_id",
       "e4"."is_silence"              AS "e4__is_silence",
       "e4"."follows_id"              AS "e4__follows_id",
       "e5"."id"                      AS "e5__id",
       "e5"."name"                    AS "e5__name",
       "e5"."statement"               AS "e5__statement",
       "e5"."solution_id"             AS "e5__solution_id",
       "e5"."last_modified"           AS "e5__last_modified",
       "e5"."modified_by_id"          AS "e5__modified_by_id",
       "e5"."is_silence"              AS "e5__is_silence",
       "e5"."parent_component_id"     AS "e5__parent_component_id",
       "e5"."follows_id"              AS "e5__follows_id",
       "f6"."id"                      AS "f6__id",
       "f6"."name"                    AS "f6__name",
       "f6"."statement"               AS "f6__statement",
       "f6"."solution_id"             AS "f6__solution_id",
       "f6"."last_modified"           AS "f6__last_modified",
       "f6"."modified_by_id"          AS "f6__modified_by_id",
       "f6"."is_silence"              AS "f6__is_silence",
       "f6"."priority"                AS "f6__priority",
       "f6"."follows_id"              AS "f6__follows_id",
       "g7"."id"                      AS "g7__id",
       "g7"."name"                    AS "g7__name",
       "g7"."statement"               AS "g7__statement",
       "g7"."solution_id"             AS "g7__solution_id",
       "g7"."last_modified"           AS "g7__last_modified",
       "g7"."modified_by_id"          AS "g7__modified_by_id",
       "g7"."is_silence"              AS "g7__is_silence",
       "g7"."parent_component_id"     AS "g7__parent_component_id",
       "g7"."follows_id"              AS "g7__follows_id",
       "i8"."id"                      AS "i8__id",
       "i8"."name"                    AS "i8__name",
       "i8"."statement"               AS "i8__statement",
       "i8"."solution_id"             AS "i8__solution_id",
       "i8"."last_modified"           AS "i8__last_modified",
       "i8"."modified_by_id"          AS "i8__modified_by_id",
       "i8"."is_silence"              AS "i8__is_silence",
       "i8"."follows_id"              AS "i8__follows_id",
       "j9"."id"                      AS "j9__id",
       "j9"."name"                    AS "j9__name",
       "j9"."statement"               AS "j9__statement",
       "j9"."solution_id"             AS "j9__solution_id",
       "j9"."last_modified"           AS "j9__last_modified",
       "j9"."modified_by_id"          AS "j9__modified_by_id",
       "j9"."is_silence"              AS "j9__is_silence",
       "j9"."follows_id"              AS "j9__follows_id",
       "l10"."id"                     AS "l10__id",
       "l10"."name"                   AS "l10__name",
       "l10"."statement"              AS "l10__statement",
       "l10"."solution_id"            AS "l10__solution_id",
       "l10"."last_modified"          AS "l10__last_modified",
       "l10"."modified_by_id"         AS "l10__modified_by_id",
       "l10"."is_silence"             AS "l10__is_silence",
       "l10"."follows_id"             AS "l10__follows_id",
       "n11"."id"                     AS "n11__id",
       "n11"."name"                   AS "n11__name",
       "n11"."statement"              AS "n11__statement",
       "n11"."solution_id"            AS "n11__solution_id",
       "n11"."last_modified"          AS "n11__last_modified",
       "n11"."modified_by_id"         AS "n11__modified_by_id",
       "n11"."is_silence"             AS "n11__is_silence",
       "n11"."priority"               AS "n11__priority",
       "n11"."follows_id"             AS "n11__follows_id",
       "o12"."id"                     AS "o12__id",
       "o12"."name"                   AS "o12__name",
       "o12"."statement"              AS "o12__statement",
       "o12"."solution_id"            AS "o12__solution_id",
       "o12"."last_modified"          AS "o12__last_modified",
       "o12"."modified_by_id"         AS "o12__modified_by_id",
       "o12"."is_silence"             AS "o12__is_silence",
       "o12"."follows_id"             AS "o12__follows_id",
       "o13"."id"                     AS "o13__id",
       "o13"."name"                   AS "o13__name",
       "o13"."statement"              AS "o13__statement",
       "o13"."solution_id"            AS "o13__solution_id",
       "o13"."last_modified"          AS "o13__last_modified",
       "o13"."modified_by_id"         AS "o13__modified_by_id",
       "o13"."is_silence"             AS "o13__is_silence",
       "o13"."follows_id"             AS "o13__follows_id",
       "p14"."id"                     AS "p14__id",
       "p14"."name"                   AS "p14__name",
       "p14"."statement"              AS "p14__statement",
       "p14"."solution_id"            AS "p14__solution_id",
       "p14"."last_modified"          AS "p14__last_modified",
       "p14"."modified_by_id"         AS "p14__modified_by_id",
       "p14"."is_silence"             AS "p14__is_silence",
       "p14"."email"                  AS "p14__email",
       "p14"."follows_id"             AS "p14__follows_id",
       "s15"."id"                     AS "s15__id",
       "s15"."name"                   AS "s15__name",
       "s15"."statement"              AS "s15__statement",
       "s15"."solution_id"            AS "s15__solution_id",
       "s15"."last_modified"          AS "s15__last_modified",
       "s15"."modified_by_id"         AS "s15__modified_by_id",
       "s15"."is_silence"             AS "s15__is_silence",
       "s15"."follows_id"             AS "s15__follows_id",
       "s15"."parent_component_id"    AS "s15__parent_component_id",
       "s15"."segmentation"           AS "s15__segmentation",
       "s15"."category"               AS "s15__category",
       "s15"."availability"           AS "s15__availability",
       "s15"."influence"              AS "s15__influence",
       "s16"."id"                     AS "s16__id",
       "s16"."name"                   AS "s16__name",
       "s16"."statement"              AS "s16__statement",
       "s16"."solution_id"            AS "s16__solution_id",
       "s16"."last_modified"          AS "s16__last_modified",
       "s16"."modified_by_id"         AS "s16__modified_by_id",
       "s16"."is_silence"             AS "s16__is_silence",
       "s16"."follows_id"             AS "s16__follows_id",
       "s16"."parent_component_id"    AS "s16__parent_component_id",
       "u17"."id"                     AS "u17__id",
       "u17"."name"                   AS "u17__name",
       "u17"."statement"              AS "u17__statement",
       "u17"."solution_id"            AS "u17__solution_id",
       "u17"."last_modified"          AS "u17__last_modified",
       "u17"."modified_by_id"         AS "u17__modified_by_id",
       "u17"."is_silence"             AS "u17__is_silence",
       "u17"."priority"               AS "u17__priority",
       "u17"."primary_actor_id"       AS "u17__primary_actor_id",
       "u17"."follows_id"             AS "u17__follows_id",
       "u17"."scope"                  AS "u17__scope",
       "u17"."level"                  AS "u17__level",
       "u17"."goal_in_context"        AS "u17__goal_in_context",
       "u17"."precondition_id"        AS "u17__precondition_id",
       "u17"."trigger_id"             AS "u17__trigger_id",
       "u17"."main_success_scenario"  AS "u17__main_success_scenario",
       "u17"."success_guarantee_id"   AS "u17__success_guarantee_id",
       "u17"."extensions"             AS "u17__extensions",
       "u18"."id"                     AS "u18__id",
       "u18"."name"                   AS "u18__name",
       "u18"."statement"              AS "u18__statement",
       "u18"."solution_id"            AS "u18__solution_id",
       "u18"."last_modified"          AS "u18__last_modified",
       "u18"."modified_by_id"         AS "u18__modified_by_id",
       "u18"."is_silence"             AS "u18__is_silence",
       "u18"."priority"               AS "u18__priority",
       "u18"."primary_actor_id"       AS "u18__primary_actor_id",
       "u18"."follows_id"             AS "u18__follows_id",
       "u18"."functional_behavior_id" AS "u18__functional_behavior_id",
       "u18"."outcome_id"             AS "u18__outcome_id"
FROM   "parsed_requirement" AS "p0"
       LEFT JOIN "app_user" AS "m1"
              ON "p0"."modified_by_id" = "m1"."id"
       LEFT JOIN "assumption" AS "a2"
              ON "p0"."id" = "a2"."follows_id"
       LEFT JOIN "constraint" AS "c3"
              ON "p0"."id" = "c3"."follows_id"
       LEFT JOIN "effect" AS "e4"
              ON "p0"."id" = "e4"."follows_id"
       LEFT JOIN "environment_component" AS "e5"
              ON "p0"."id" = "e5"."follows_id"
       LEFT JOIN "functional_behavior" AS "f6"
              ON "p0"."id" = "f6"."follows_id"
       LEFT JOIN "glossary_term" AS "g7"
              ON "p0"."id" = "g7"."follows_id"
       LEFT JOIN "invariant" AS "i8"
              ON "p0"."id" = "i8"."follows_id"
       LEFT JOIN "justification" AS "j9"
              ON "p0"."id" = "j9"."follows_id"
       LEFT JOIN "limit" AS "l10"
              ON "p0"."id" = "l10"."follows_id"
       LEFT JOIN "non_functional_behavior" AS "n11"
              ON "p0"."id" = "n11"."follows_id"
       LEFT JOIN "obstacle" AS "o12"
              ON "p0"."id" = "o12"."follows_id"
       LEFT JOIN "outcome" AS "o13"
              ON "p0"."id" = "o13"."follows_id"
       LEFT JOIN "person" AS "p14"
              ON "p0"."id" = "p14"."follows_id"
       LEFT JOIN "stakeholder" AS "s15"
              ON "p0"."id" = "s15"."follows_id"
       LEFT JOIN "system_component" AS "s16"
              ON "p0"."id" = "s16"."follows_id"
       LEFT JOIN "use_case" AS "u17"
              ON "p0"."id" = "u17"."follows_id"
       LEFT JOIN "user_story" AS "u18"
              ON "p0"."id" = "u18"."follows_id"
WHERE  "p0"."solution_id" = '0191e829-4c27-7669-b5a0-f2987e5c7a60'
       AND "p0"."name" = '{LLM Parsed Requirement}' 

Note the execution time: [took 21 ms, 25 results] and the fact that not all requirement types are represented.

This is in part a consequence of choosing the Table Per Concrete Class (TPCC) Inheritance Strategy. This approach seems to prevent a straightforward representation of requirement relations such as X disjoins Y, X contradicts Y, X follows Y, etc.

By migrating to a Single Table Inheritance strategy, and introducing a Relation table for inter-requirement relations, this will solve many problems and open up more expressiveness in general. I suspect a significant reduction in code size as well and fix some other downstream hacks and awkwardness in the code

[mlhaufe]

The new execution time is is now [took 12 ms, 25 results]

The Schema is now generalized.

Before

After