0

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.

Improve this question
4
  • 1
    How many students? 50*300? Or 50*300*5?
    – Rick James
    Dec 26, 2016 at 20:25
  • 50 (departments) * 300 (possible students) * 5 (levels, that is like saying grade 1 to grade 5) * 30 (rows for courses) = 2, 250,000 (possible rows consumed in a session).
    – BlackPearl
    Dec 26, 2016 at 20:28
  • 1
    How many different humans? That would be the size of one of the tables. A many-to-many table might have 2M rows.
    – Rick James
    Dec 26, 2016 at 20:33
  • update please, i would like to know what up and downs you have had with the decisions you have gone for...., I have my own system and am also serializing rows, and would like to know what your experiences has been so far.
    – Seabizkit
    Dec 20, 2019 at 7:35

1 Answer 1

Reset to default
1

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 JOINing to 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.

Improve this answer
2
  • Thanks for trying to lead me in the right direction. It is actually 2, 250,000. I know much about JOINs too, just that I feel with that number of rows stored per year, it may grow too large in a few years and that is why I am considering serialisation. I wanted to know if storing data serially is a bad idea from the database point of view.
    – BlackPearl
    Dec 26, 2016 at 20:19
  • The real question comes to what the SELECTs look like.
    – Rick James
    Dec 26, 2016 at 20:27

Your Answer

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

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