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]
vs
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?
Additional info
- 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.
Aucun commentaire:
Enregistrer un commentaire