Search code examples
databasedatabase-designdata-structuresrelational-databaseentity-relationship

How best to design database structure for this case?

I'm designing a database for my University applications. There's Report entity of which is the following:

enter image description here

Each report has a list of students, courses and students rating on them.

The problem is that over time, courses and number of students may change. The database structure should be flexible and easily adaptable to these changes. How best to define the relationships between these entities and design tables in a relational database for this case?

I have some thoughts on the matter. Perhaps this model best describes the relationship and solves problem of the issue:enter image description here

Or is the relationship REPORT to STUDENT superfluous and redundant? Is there enough of one relationship for a REPORT? For example only REPORT to COURSE without REPORT to STUDENT:enter image description here Which choice is better and why? Does this structure have any other disadvantages?

Thanks in advance for your suggestions!

Solution

  • "the report is a set of ratings?" Yes

    OK, that clears things a little bit. I'm still unceratin as to what exactly are you trying to model, but I'll give it a stab:

    enter image description here

    The RATING PK1 ensures no student can rate the same course more than once. A report is simply a set of ratings.

    This structure does not ensure that all ratings in the report are related to the same course, nor it enforces the course attendance in any way2.

    1 Specifically, the fact that REPORT_ID is outside the PK, so one rating cannot be part of multiple reports. If you want the opposite, you cannot just include that field in the PK since that would allow for different VALUEs - you'll need a proper junction table instead.

    2 You might want to forbid a rating of a course by a student that did not attend the course. Let me know how you intend to represent the course attendance and I might have some ideas how to connect that to ratings and reports...