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