CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008database-design
Darf
Darf

Reputation: 2585

Create a calculated field using data from another tables

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

Database design

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

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.

Upvotes: 3

Views: 1749

Answers (2)

Yuck
Yuck

Reputation: 50825

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 believe you can create computed columns in this way. Can you use a view with a CASE statement? Something (roughly) like this:

CASE WHEN Score >= 0.70
  THEN 1
  ELSE 0
END AS IsAccredited

The value for Score may itself be the result of aggregating an average of all exam scores. It's hard to tell from your model which fields currently exist and which are only conceptual.

Upvotes: 0

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74340

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.

Upvotes: 1

Related Questions