lundi 26 janvier 2015

What is the best design for storing courses subscription data?


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



  1. 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.

  2. 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