0

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.
  • Can you more clearly describe subscriptions? What are they? Perhaps if you add a few more columns to the table, it will be a bit clearer? Given that information, I think you'll get better design guidance. – Mark Iannucci Jan 23 '15 at 16:37
  • @MarkIannucci Can you please check the additional info section if it provides enough information to give an insight, I've also added a missing StudentId column to the tables. – Jyrkka Jan 24 '15 at 9:57
1

Both are OK designs, so now the problem is deciding between the two. Here are some things that I think you should consider as you decide which one is best.

  • How similar are the non-essential columns in the Course Subscription and Lesson Subscription tables? If they are very similar then records which contain only a course subscription in the Subscriptions table would not have a bunch of null columns to skip which apply only to lesson subscriptions and vice versa. The more similar the columns are, the more you should lean towards using the Subscriptions design.
  • Can a student subscribe to both a course and a lesson at the same time? If the student does that, will the data for any shared columns between a course and lesson be different? If the data is different, then you'll definitely need the CourseSubscriptions and LessonSubscriptions tables. Otherwise, you should be fine with just a Subscriptions table.

Finally, if you go with one table... Think through how your output would handle a combined course and lesson subscription. Is that two subscriptions or one? Conversely, if you go with two tables and one action results in two entries, is that one or two?

  • I know, answering a question with a bunch of other questions is generally frowned upon. @Jyrkka, if you post back more additional information, I'll edit the answer and hopefully this will become more of a design conversation thread from which others may benefit. – Mark Iannucci Jan 26 '15 at 4:30
  • I added additional info answering some of your questions. Theoretically there is an option that some student will have a course subscription, but he would like to buy also a single lesson of the same course, to give it to a friend or something. Of course the option where a student has several subscriptions to different courses, and some lessons which belong to other course. The columns are very similar, the CourseSubscriptions has some additional info columns which aren't necessary for LessonSubscriptions. – Jyrkka Jan 26 '15 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.