SQL - database design, need suggestion

Question

I'm building website where each user has different classes

+----+-----------+---------+
| id | subject   | user_id |
+----+-----------+---------+
|  1 | Math 140  |       2 |
|  2 | ART 240   |       2 |
+----+-----------+---------+

Each class then will have bunch of Homework files, Class-Papers files and so on. And here I need your help. What will be the better approach: Build one table like that:

+----+-----------+--------------------------------------------------+--------------+
| id | subject   | Homework                                         | Class-Papers |
+----+-----------+-----------------------------------------------------------------+
|  1 | Math 140  |  www.example.com/subjects/Math+140/file_name.pdf |   bla-bla    |
|  2 | Math 140  |  www.example.com/subjects/Math+140/file_name.pdf |   bla-bla    |
|  3 | Math 140  |  www.example.com/subjects/Math+140/file_name.pdf |   bla-bla    |
|  4 | ART 240   |  www.example.com/subjects/ART +240/file_name.pdf |   bla-bla    |
|  5 | ART 240   |  www.example.com/subjects/ART +240/file_name.pdf |   bla-bla    |
+----+-----------+--------------------------------------------------+--------------+

And than just separate the content when I want to display it, OR build a table for every single subject and than just load necessary table?

Or if you can suggest something better or more common/useful/efficient please go ahead.

Answer 1

You should read about normalization and relational design before attempting this.

This is a one-to-many relationship - model it as such.

A table for every subject is crazy. You'll have to add a new table for every subject.

A better solution will make it possible to add new subjects simply by adding data. That's what the relational model is all about.

Don't worry about tables; think about it in natural language first.

A SUBJECT(calculus) can have many COURSES(differential, integral, multi-variable).

A COURSE(differential calculus) can have many SECTIONs (Mon 9-10 am in room 2 of the math building).

A STUDENT(first name, last name, student id) can sign up for zero or more SECTIONs. The list of SECTIONs for a given STUDENT is a TRANSCRIPT.

Each STUDENT has one TRANSCRIPT per semester (fall 2012).

A SECTION can have zero or more ASSIGNMENTs.

These are the tables you'll need for this simple problem. Worry about the names and how they relate before you start writing SQL. You'll be glad you did.

Answer 2

I most definitely woudl NOT create a separate table for each subject. If you did, then if you wanted a query like "list all the homework for student X", you would have to access different tables depending on which subjects that student was enrolled in. Worse, anytime someone added a new subject, you would have to create a new table. If down the road you decide you need a new attribute of homework, instead of updating one table, you would have to update every one of these subject tables. It's just bad news all around.