I've several many to a lot of relations during my model composed of the client, a regular membership, a training course:

  1. A customer has zero or even more monthly subscriptions
  2. A regular membership enables the customer to gain access to a number of courses

I curently have three tables that list all of the clients, subscription plans and courses. What will be the most practical way to implement the numerous-to-many relations without needing to duplicate lots 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.

Listed here are the relations:

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

Explanation: each subscription is specificially for just one client, so there's single:d relationship between individuals two. However the same course could be reserved more often than once by different clients via different monthly subscriptions, so there's a n:m relationship between courses and monthly subscriptions, that is resolved with a link table Subscription_Course.

You can include additional constraints on that model if you would like, for instance, put a distinctive key constraint on (SubscriptionID, CourseID) in Subscription_Course.

enter image description here


ClientSubscriptionNo is really a subscription number for every client (1,2,3..) it may be easily produced when designing a brand new subscription for any client using

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

You might choose to:

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

One table with clientId, subscriptionId and the other table with subscriptionId and courseId

Common method of store many-to-many between two tables would be to put secrets from both tables towards the third table such as this

enter image description here