Create a calculated field using data from another tables

Question

I'm creating a database design for only a subject school. My current design is showed in the next link:

I've enclosed three entities and all of then contains calculated fields:

• StudetExams: Average
• StudentExamObjectives: Accredited and Score
• ObjectiveGrades: FinalScore

But they need to interact with another tables. For example, StudentExamObjectives use a Score field, where is the avg for all the problems correct in Answers table, then accredited is 1 if Score values is above or equals than 0.70.

I don't know if my design is wrong or I need to do a trick to get those values.

Thanks in advance.

Answer 1

The problem you are experiencing is due to a lack of normalization. Whenever you have a need for a function of the values of columns of other tables, you shouldn't be storing it in a column in a table - you should be calculating it on demand as part of the query that requires this information. You can simplify this with an inline table-valued function (TVF) returning a table with a single or possibly multiple column(s).

Otherwise, you have to calculate and store the result during inserts, causing data redundancy and the possibility of an incoherent view of your data if any of the columns from which a value is calculated changes or its row is removed.

There are times when such redundancy is added via denormalization, but this should only be done in the worst case, when profiling your database with real world scenarios forces you in this direction. If you go this route, be sure to add steps that make sure that there is integrity in your database after any transactional operation.