Обсуждение: Relational loops in a DB

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.
>
> One "course" can be offered at several dates (course offerings) and also
> one "course" can be thought by several "qualified trainers". A "course
> offering" is taught by one "qualified trainer".
>
> I will very much appreciate your feedback. I hope my explanation is clear
> enough.
>
> Respectfully,
> Jorge Maldonado

Note that you haven't actually asked a question here...

It would have been more helpful to add cardinality indicators to the visual
than try and explain it using text.

Anyway, course-trainer seems to be a many-to-many relationship and so
requires a separate linking table in the model.

A trainer does not require a course or offering so that is an optional link
which breaks any kind of circle.

The course_offering FK would be against the "course-trainer" link table and
not directly against course or trainer.

[forgive if I get the arrow direction wrong - you'll get the idea]

course <- course_trainer -> trainer

course_trainer <- course_offering -> calendar

You should seriously considering implementing the foreign keys using
multi-column keys instead of generating a serial/surrogate on course_trainer
and using that single field on course_offering.

Note that in this model a course does not require any trainers but it cannot
be offered until there is at least one.

David J.








--
View this message in context: http://postgresql.1045698.n5.nabble.com/Relational-loops-in-a-DB-tp5796019p5796021.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

I am designing a DB and some of the tables have a loop in their relationships. I am attaching an image of such tables.

One "course" can be offered at several dates (course offerings) and also one "course" can be thought by several "qualified trainers". A "course offering" is taught by one "qualified trainer".

I will very much appreciate your feedback. I hope my explanation is clear enough.

Respectfully,

Jorge Maldonado

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

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