Re: 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.