From: | Adam Mackler <postgresql(at)mackler(dot)org> |
---|---|
To: | JORGE MALDONADO <jorgemal1960(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Relational loops in a DB |
Date: | 2014-03-14 14:31:09 |
Message-ID: | 20140314143109.GD18712@scruffle.mackler.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Actually, now I see you want to keep track of qualifications to
prevent an unqualified trainer from offering a course. I'll change my
schema by adding a fourth table, `expertise`, and change the foreign
keys of the `schedule` table to reference that instead of referencing
the `course_catalog` and `staff` tables. Like so:
sandbox=> \d expertise
Table "public.expertise"
Column | Type | Modifiers
------------+--------------+-----------
trainer_id | integer | not null
course_id | character(3) | not null
Indexes:
"expertise_pkey" PRIMARY KEY, btree (trainer_id, course_id)
Foreign-key constraints:
"expertise_course_fkey" FOREIGN KEY (course_id) REFERENCES course_catalog(course_id)
"expertise_trainer_fkey" FOREIGN KEY (trainer_id) REFERENCES staff(trainer_id)
Referenced by:
TABLE "schedule" CONSTRAINT "schedule_expertise_fkey" FOREIGN KEY (trainer_id, course_id) REFERENCES expertise(trainer_id, course_id)
sandbox=> \d schedule
Table "public.schedule"
Column | Type | Modifiers
--------------+----------------------+-----------
offering_id | character(4) | not null
course_id | character(3) | not null
trainer_id | integer | not null
classroom_id | character varying(5) | not null
start_date | date | not null
Indexes:
"schedule_pkey" PRIMARY KEY, btree (offering_id)
"schedule_ukey" UNIQUE CONSTRAINT, btree (course_id, start_date)
Foreign-key constraints:
"schedule_expertise_fkey" FOREIGN KEY (trainer_id, course_id) REFERENCES expertise(trainer_id, course_id)
--
Adam Mackler
From | Date | Subject | |
---|---|---|---|
Next Message | Daryl Foster | 2014-03-14 16:58:55 | Re: Cannot insert to 'path' field using EclipseLink |
Previous Message | Adam Mackler | 2014-03-14 14:11:29 | Re: Relational loops in a DB |