Database model optimization

1

I'm making a system for a college and one of the subsystems I'm building is the student newsletter. I put it to when creating a new discipline in the table "discipline" this discipline is added to the "notes" table with a record for each id of each student enrolled in the course which the discipline belongs and with the field note in these new registers. p>

The notas table would look something like this:

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

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

Are there any better and more optimized ways to do this?

    
asked by anonymous 08.10.2017 / 19:11

2 answers

2

Essentially not, unless you change the requirement. And it does not have any unspoken requirements.

I also do not think you will have as much data as you think there will be. Even less that there will be performance issues, unless you know something I do not know about the problem.

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

Obviously a proper 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.

    
08.10.2017 / 19:27
1

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

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

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

    
08.10.2017 / 23:50