I am currently designing a database table where I store list of courses chosen by students for a session. My current design has to do with storing each course in a row and a student can have up to 30 courses per session. This means that 300 students in that single department will consume 30*300 = 9000 rows for just a session. There over 50 departments in the School and each department has at least 300 students per level (5 levels) and the minimal courses taken per session is up to 30. The system is designed in such a way that these data stored will not be deleted.
Meaning:
50 (departments) * 300 (possible students) * 5 (levels) * 30 (rows for courses) = 2, 250,000 (possible rows consumed in a session).
Serialized Format
Is it better to store a student courses in a serialized format that use just a row to save it?
50 (departments) * 300 (possible students) * 5 (levels) * 1 (rows for courses) = 75,000 (possible rows consumed in a session).
I calculated this and I think it makes sense but I don't know if it is a faulty design.
Low Point
I recognised that update and deletion query will overwrite/delete everything in the courses column.
Please an insightful help will be appreciated.
Note: I use MySql for my database.
Best Answer
2M rows is "medium-sized". That should not be your concern; databases are designed to handle millions, even billions, of rows. However you will need to have suitable indexes, and include the optimal
PRIMARY KEY.Think about what "entities" you have -- departments, students, sessions, courses, etc. Thin think about the "relationships" -- course in department; student in a session of a course; etc.
1:many is handled by an id in one table for
JOINingto another table. many:many is handled by an extra table with two ids in it. 1:1 is (usually) a no-no.Work through some tutorials on database design. Be sure to understand
JOINs.