Building an app that will manage lessons, students, courses. Each lesson belongs to a certain course. A student can subscribe to a single lesson or to a whole course. So it seems straightforward that I need these tables(only essential columns):
Students -------- Id[PK] Lessons ------- Id[PK] CourseId[FK, NOT NULL] Courses ----------- Id[PK]
But how should I store subscriptions? Should I store them in one table that has two columns LessonId and CourseId only one of which will be populated for each subscription, or should I create two similar tables managing lessons and courses individually?
Subscriptions ------------- Id[PK] StudentId[FK, NOT NULL] LessonId[FK, NULL] CourseId[FK, NULL]
CourseSubscriptions ------------------- Id[PK] StudentId[FK, NOT NULL] CourseId[FK, NOT NULL] LessonSubscriptions ------------------- Id[PK] StudentId[FK, NOT NULL] LessonId[FK, NOT NULL]
Or may be you can advise another more suitable approach?
- Table(s) of subscriptions can be thought of as an intent of some person(student) to participate in a specific lesson(since he has an interest in subject) or in whole bunch of lessons that are grouped under the hood of a course.
- A student can subscribe to as many lessons and courses as he likes, I think that each subscription should have a record of its own, so in case of a shared table there wont be any data overlapping.