Search code examples
databaserelational-databasetable-relationships

Relationships between tables

I have a table called objectives, each objective has zero to many cause-effect relationships with other objectives, these relationships I have to be stored in the database, let me know if there's a way to relate this table records.

Solution

  • There is not a way to relate the records without creating an additional table (you would need N-1 additional columns on your current table to model the N possible effects of a cause).

    Creating an additional table like the one below should serve your purpose.

    CREATE TABLE cause_effect (
  cause integer NOT NULL,
  effect integer NOT NULL,
  CONSTRAINT cause_effect_pkey PRIMARY KEY (cause, effect),
  CONSTRAINT cause_effect_cause_fkey FOREIGN KEY (cause)
      REFERENCES yourtable (id),
  CONSTRAINT cause_effect_effect_fkey FOREIGN KEY (effect)
      REFERENCES yourtable (id)
)

    Apply FKey behaviour as applies.