Re: Relational loops in a DB

On Thu, Mar 13, 2014 at 06:18:32PM -0600, JORGE MALDONADO wrote:
> I am designing a DB and some of the tables have a loop in their
> relationships. I am attaching an image of such tables.

Here's my shot at it: three tables: `course_catalog`, `staff` and `schedule`:

sandbox=> \d course_catalog
Table "public.course_catalog"
Column | Type | Modifiers
-------------+-----------------------+-----------
course_id | character(3) | not null
course_name | character varying(64) | not null
Indexes:
"course_catalog_pkey" PRIMARY KEY, btree (course_id)
Referenced by:
TABLE "schedule" CONSTRAINT "schedule_course_fkey" FOREIGN KEY (course_id) REFERENCES course_catalog(course_id)

sandbox=> \d staff
Table "public.staff"
Column | Type | Modifiers
--------------+-----------------------+-----------
trainer_id | integer | not null
trainer_name | character varying(64) | not null
Indexes:
"staff_pkey" PRIMARY KEY, btree (trainer_id)
Referenced by:
TABLE "schedule" CONSTRAINT "schedule_trainer_fkey" FOREIGN KEY (trainer_id) REFERENCES staff(trainer_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_course_fkey" FOREIGN KEY (course_id) REFERENCES course_catalog(course_id)
"schedule_trainer_fkey" FOREIGN KEY (trainer_id) REFERENCES staff(trainer_id)

Here's the SQL:

CREATE TABLE course_catalog (
course_id character(3) NOT NULL PRIMARY KEY,
course_name character varying(64) NOT NULL
);

CREATE TABLE staff (
trainer_id integer NOT NULL PRIMARY KEY,
trainer_name character varying(64) NOT NULL
);

CREATE TABLE schedule (
offering_id character(4) NOT NULL PRIMARY KEY,
course_id character(3) NOT NULL,
trainer_id integer NOT NULL,
classroom_id character varying(5) NOT NULL,
start_date date NOT NULL,
CONSTRAINT schedule_ukey UNIQUE (course_id, start_date),
CONSTRAINT schedule_course_fkey FOREIGN KEY (course_id) REFERENCES course_catalog(course_id),
CONSTRAINT schedule_trainer_fkey FOREIGN KEY (trainer_id) REFERENCES staff(trainer_id)
);

If you want some reading material that might help you to develop this
skill (besides general relational DB theory), I'll recommend
"Designing Quality Databases with IDEF1X Information Models," by Thomas
A Bruce.

http://www.amazon.com/Designing-Quality-Databases-IDEF1X-Information/dp/0932633188

Don't let the age fool you. I found it very helpful and you can get
it used for the price of postage.

--
Adam Mackler