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.

  • 1
    How many students? 50*300? Or 50*300*5? – Rick James Dec 26 '16 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 '16 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 '16 at 20:33
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.

  • 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 '16 at 20:19
  • The real question comes to what the SELECTs look like. – Rick James Dec 26 '16 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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