SQL Server and Calculated Fields?

Question

So I'm creating an app that with users who can earn "points" in many different ways. Some of these point accruals occur because of their profile, actions they've taken, etc. (i.e. spread across multiple tables).

I don't want to manually add points in a field when certain actions occur because I want to ensure number consistency. I would like to have some sort of calculated field that keeps their points updated in a field for easy querying. This is because I don't want to run an insanely complex select/view every time I want to list a set of users and their points (like a top 100 list).

Is there a way to compute a field in the users table using a complex select statement over multiple other tables? Is it efficient? Should I just ditch the computed field and go with a well written procedure?

Answer 1

Your approach sounds reasonable, often we need to de-normalize SQL designs once they move into production and start taking a hit.

There are many ways to do this. Here's one way to do this using T-SQL:

SELECT
    u.Id,
    u.UserName,
    t.Point_Total
FROM User u
INNER JOIN (
     SELECT Id, SUM (Points) AS Point_Total
     FROM (
        SELECT Id, Points FROM TableA
        UNION ALL   --Be **SURE** to include "ALL"
        SELECT Id, Points FROM TableB
        UNION ALL
        SELECT Id, 5 AS Points FROM SpecialCondition
     GROUP BY Id
     ) t ON t.Id = u.Id

There are many other ways depending on your schema and data distribution, so you'll have to experiment and track your performance.

Answer 2

If it spreads accross multiple tables, I would recomend a view, using these tables, or a userdefined table/scalar (depending on your requirements) function to retrieve these values.

Answer 3

Why not keep the points details in a single table? Rows in the table could be something like UserId, Item, and Points -- so a given user could have multiple rows, with varying points by item, and you could easily sum the total for reporting purposes.

Regarding your concern about ensuring number consistency, you could just make the adjustments in a transaction. That would be easy to manage with a collection of stored procedures that manage your data access. For example, you mentioned 5 points for the existence of certain data in their profile -- so when that data is added to their profile, in the same transaction, you would INSERT a new row into the Points table.

Otherwise, if you try to manage this kind with multiple joins, and if you need to report the numbers very often, it's likely to get very slow, very quickly.