Relationships between tables

Question

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.

can you write som sample data, please. – dani herrera Dec 08 '11 at 19:34 — dani herrera, Dec 08 '11 at 19:34

score 0 · Accepted Answer · answered Dec 08 '11 at 19:31

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.

Relationships between tables

1 Answers1