Postgresql – What schema for different relation from two tables to another

postgresqlschema

There are three tables instructors, meeting_points and bookings. Here are the relation I want:

  • 1 instructor has 0, 1 or n meeting_points
  • 1 booking has 1 meeting_point
  • 1 meeting_point can be linked to 1 instructor + n bookings or just 1 instructor
  • There are no direct relation between instructors and bookings

I don't know how to represent this properly in a PostgreSQL table (or even in MySQL or other SQL). I can use a foreign key on instructor id in meeting_points table and and add a foreign key on meeting_point id in bookings table but I'm not sure it's the best way (I think it's confusing). How can I represent these relations? Do I need a pivot table for meeting_points with nullable relations?

It's my first post on dba stackexchange, I hope it's clear enough and not a duplicate post (or off topic)

EDIT – Reading answers, I think I must add a little more information.

Meeting points are declared by instructors. So, an instructor says: "you can meet me at X, Y or Z point". And other people (let says these are learners) can book a lesson in a particular meeting point: this is a booking.

In other words, the relation between instructor and meeting point is: an instructor declares available meeting point with him to potential learners. And a learner (learner does not matter in my question) says: "ok, I ask for a booking in that meeting point" (the meeting point is one of the available meeting point declared by the instructor).

Now, other (maybe useless) information:

  • Meeting points are not shared between instructors, instructors choose and "write" their own named and geo-localized meeting point (it can seems strange, but trust me!).
  • A booking has different status (accepted, pending, canceled) and is "created" by learners, so there can be n bookings for one lesson on x meeting points (lesson does not matter in this question). But the meeting points are only one of the meeting declared by the instructor who made lesson available.
  • Bookings have one foreign key on lesson, and one foreign key on learner.

Best Answer

Rearranging / rephrasing your relationships a bit:

  • 1 instructor has 0, 1 or n meeting_points.
    1 meeting_point is linked to 1 instructor.

    This means there is a simple 1:n (1::0-n) relationship between them. The foreign key should be in meeting_points, referencing instructors.

  • 1 booking has 1 meeting_point.
    1 meeting_point is linked to 0 or 1 booking.

    This means there is a simple 1:1 (1::0-1) relationship between them. The foreign key should be in bookings, referencing meeting_points.

  • There are no direct relationships between instructors and bookings.

    The implementing is obvious here.

Without further information about other attributes and possible unique keys, I'll use the above, adding surrogate unique keys in the first two tables (this is not needed, the surrogate keys could be replaced by other unique keys you may have identified). The bookings table does not need a surrogate anyway, as it's in a 1:1 relationship with meeting_points and the same column can be used as primary key and for the foreign key constraint:

CREATE TABLE instructors 
(   instructor_id SERIAL NOT NULL,
    instructor_name TEXT NOT NULL,
    -- other attributes
    PRIMARY KEY (instructor_id)
);

CREATE TABLE meeting_points 
(   meeting_point_id SERIAL NOT NULL,
    meeting_point_name TEXT NOT NULL,
    -- other attributes
    instructor_id INT NOT NULL,
    PRIMARY KEY (meeting_point_id),
    FOREIGN KEY (instructor_id)
        REFERENCES instructors (instructor_id)
);

CREATE TABLE bookings 
(   booking_id INT NOT NULL,
    booking_date DATE NOT NULL,
    -- other attributes
    PRIMARY KEY (booking_id),
    FOREIGN KEY (booking_id)
        REFERENCES meeting_points (meeting_point_id)
);

Sidenote: The relationship "a meeting_point has (max) one booking" does not sound very realistic. So, if A is an instructor and M is a meeting_point, A can only make one booking in M? What if she wants to make another booking in the same point tomorrow? This would not be allowed under this design.

Related Question