Vor Vor - 1 month ago 10
MySQL Question

SQL - database design, need suggestion

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 Source

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.