1

I'm developing a site that will be a subscription based service that will provide users several courses (videos) based on whatever they signed up for.

Currently I was considering the following db structure

Users
------

email | pwd | subscription_date | expiration_date

Unique Key would be "email"

Course_Subscription
-------

email | calculus_1 | calculus_2 | physics_1 | physics_2 ... | Nth course

the number of courses offered will probably start at approx 15, and gradually increase overtime. Also the course subscriptions will be boolean value of TRUE/FALSE

then a table for each course as follows:

Calculus_1
----------
id | title | description | video_url |

there could be more than 20 chapters in a single course.

A little bit of info on authentication process - it will go userlogin ---> course subscription ---> course chapters. Where both user login and course subscription will be verified against the email address. The videos will also be checked against the subscription table prior to being played.

My questions are:

  1. Is this the best way to structure this? Or are there better alternatives?
  2. Would this cause any problems in terms of performance? Or would it not be noticeable?

Here is a sample of a php script that I'll use to authenticate and populate the html

$sqlSubscription = "SELECT * FROM course_subscription WHERE `user` = $user && `calculus_1` = TRUE";
$subscriptionResult = mysql_query($sql) or mysql_die($sqlSubscription);
while ($row = mysql_fetch_assoc($subscriptionResult))
{
      $user=$row["email"];
      $calculus_1 =$row["calculus_1"];
      if($user==1 && calculus_1==TRUE)

{

   $sql = "SELECT * FROM calculus_1 ORDER BY `id`";
$result = mysql_query($sql) or mysql_die($sql);
if (mysql_num_rows($result) > 0)
      {
        $data = array();
        while ($row = mysql_fetch_assoc($result))
        {
            $data[] = $rows;
                    echo "HTML THAT WILL CREATE A LIST BASED ON TABLE INFO"
          }
       }


  } 
}

The above code is for the menu that will populate a list of chapters based on their subscription. I know the code isn't perfect, I'm still working on it - but I wanted to nail down the structure of the db first now that I have a fairly decent idea of how I'll be accessing the information.

Improve this question
1
  • In short, NO, this is not a good schema. Commented Sep 8, 2013 at 8:41

2 Answers 2

Reset to default
5

A design that requires changing that data model by adding new columns and tables just to add new data, like courses, is not a good design. You should have a table with courses and a table for subscriptions.

Also, email is not a very good choice of primary key. The primary key should never change, but people may want to change their email address. For this reason an autoincrement number is often used as the pk.

A schema that takes these suggestions into account would look like this:

students             courses           subscriptions
---------------      -----------       -------------
student_id           course_id         subscription_id
email                name              student_id (foreign key to students table)
name                 description       course_id  (foreign key to courses table)
subscription date    video_url

You may have to adapt this to your specific requirements. For example the subscription expiry date: If the expiry date depends only on the the student and not on the course it can make sense to have it in the students table like you do now. But, if a student can subscribe to different courses at different dates and you want the possibility of having a different expiry date for each subscription it should be in the subscriptions table instead.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

That makes sense, thanks I completely forgot to consider people changing their email address!
2

Your design would be horrible to maintain over time. What you are proposing means that every new course would add one more column to your course_subscription table, and a new table to the database.

I'd go for a structure where you have a user table, a course table describing the individual courses (including the video url, etc.), and a user_course_subscription table, that basically consists of a user_id and a course_id, and the start-date and end-date.

This removes the requirement of having a single column for every course, while still allowing you to add students to multiple courses. It's an example of the pretty standard "many to many" relationship, where the junction table (in this case user_course_subscription) just adds the link between the other two entities.

Improve this answer

4 Comments

How would I go about listing the chapters though, which is separate for each course. Would I still make a table for the specific course? Or is there another way I should do that. Because while I can see how creating the user and course tables will workout better, I still don't have a place to put the information on the specific chapters which will vary based on course. So would it be better to have a separate table for the individual courses as well?
@Fahas - In that case, add a 4th table for that, referencing the course_id, and then the basic info you have in your current calculus_1 table design. So you'd have a course_chapter design with course_id, chapter_id, and then the specific info. This will allow you to add as many chapters to courses as you need, without the use of 1 table per course.
Oh I get it, that makes a lot of sense! Thank you very much!
One last thing, in the user_course_subscription table - what would happen if a user is registered in multiple courses with only 1 course_id column?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.