Geek asks and answers

How to realize the multiple relationships of many?

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

  • Customer has zero or more subscriptions
  • Subscription allows a customer to access one or more courses

I already have three tables listing all customers, subscription plans, and courses. What would be the best method for implementing many-to-many relationships without duplicating large amounts of data?

+3
source share
4 answers

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 ratios:

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

: , 1: n. , n: m , Subscription_Course.

, , , (SubscriptionID, CourseID) Subscription_Course.

+4

enter image description here

ClientSubscriptionNo - (1,2,3..);

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

:

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

clientId, subscriptionId subscriptionId courseId

+1

A general approach to storing many-to-many between two tables is to put the keys from both tables into a third table, like this

enter image description here

+1
source

More articles:


All Articles