How to implement multiple many to many relations?

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 clie开发者_Python百科nt 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?

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.

Note:

ClientSubscriptionNo is a subscription number for each client (1,2,3..); it can be easily generated when creating a new subscription for a client using

select coalesce(max(ClientSubscriptionNo), 0) + 1
from Subscription
where ClientID = the_client_id

You may or may not decide to:

alter table SubscriptionItem
  add constraint uq1_SubscriptionItem unique (ClientID, CourseID);

One table with clientId, subscriptionId and another table with subscriptionId and courseId

Common approach to store many-to-many between two tables is to put keys from both tables to the third table like this