Optimization of database model

Asked

Viewed 63 times

1

I’m making a system for a college and one of the subsystems that I’m building is the student report card. I put for when creating a new discipline in the "discipline" table this discipline is added to the "grades" table with a record for each id of each student enrolled in the course that the discipline belongs to and with the note field in these new records.

The table notas would be more or less like her structure:

id int(11)
iddisciplina int(11)
idcurso int(11)
idaluno int (11)
nota int(11)

But doing it this way, with a large number of students it would generate a lot of data and when I needed to delete a course for example, I would have to go around sweeping all the notes related to this course and disciplines and this would have a great performance cost.

There’s some better, more optimized way to do this?

mysql performance modeling

2 answers

2


Essentially not, unless you change the requirement. And you have no unspoken requirement.

I don’t think you’ll have as much data as you think there will be. Even less that there will be performance issues, unless you know something I don’t know about the problem.

The optimization in this case is not in the data structure but in the adopted algorithm, which is not in the question.

Obviously a suitable index is always critical. If you need to delete a course a simple command will be executed and quickly executed if you have course index.

  • There are some other related items like student files, forums, messages etc... I didn’t put the code in because it’s too long, so I thought I’d just ask the essentials. I imagined that there would be no other way than relations by indices, but I asked to remove this doubt that I was having. The first version of this system was taking a little too long to do these operations but I believe it is because it was in a shared hosting and with few processing resources.

  • I think it is by problem elsewhere. But I have no way of knowing where without knowing the system.

1

The structure you arrived at corresponds to the third normal form, in which duplicate information is not allowed in the database.

Creating and maintaining good ratings is the next step in terms of optimization.

Another thing you can do is maintain referential integrity by removing or updating cascading records whenever there is deletion or alteration of a primary key. Most relational databases already do this automatically as long as you configure them properly.

Browser other questions tagged mysql performance modeling

You are not signed in. Login or sign up in order to post.