How to implement multiple many to many relations?

Question

I have several many to many relations in my model consisting of a client, a subscription, a course:

1. A client has zero or more subscriptions
2. A subscription allows the client to access one or more courses

I already have three tables that list all the clients, subscription plans and courses. What would be the best method to implement the many-to-many relations without having to duplicate a lot of data?

Answer 1

Use 4 tables:

Client  (PK: ClientID)
Subscription (PK: SubscriptionID, FK: ClientID)
Course (PK: CourseID)
Subscription_Course (PK: Subscription_Course, FK: SubscriptionID, CourseID)

PK=Primary Key, FK=Foreign Key.

Here are the relations:

Client -> Subscription (1:n)
Subscription -> Subscription_Course (1:n)
Course -> Subscription_Course (1:n)

Explanation: each subscription is specificially for one client, so there is a 1:n relationship between those two. But the same course can be booked more than once by different clients via different subscriptions, so there is a n:m relationship between courses and subscriptions, which is resolved by a link table Subscription_Course.

You can add additional constraints on that model if you want, for example, put a unique key constraint on (SubscriptionID, CourseID) in Subscription_Course.