I'm making a little web based game and need to determine where to put logic that checks the integrity of some underlying data in the sql database.
Each user keeps track of points assigned to him, and points are awarded by various tasks. I keep a record of each task transaction to make sure they're not repeated, and to keep track of the value of the task at the time of completion, since an individual award level my fluctuate over time.
My schema looks like this so far:
create table player (
player_ID serial primary key,
player_Points int not null default 0
);
create table task (
task_ID serial primary key,
task_PointsAwarded int not null
);
create table task_list (
player_ID int references player(player_ID),
task_ID int references task(task_ID),
when_completed timestamp default current_timestamp,
point_value int not null, --not fk because task value may change later
constraint pk_player_task_id primary key (player_ID, task_ID)
);
So, the player.player_Points should be the total of all his cumulative task points in the task_list.
Now where do I put the logic to enforce this?
Should I do away with player.player_Points altogether and do queries every time I want to know the total score? Which seems wasteful since I'll be doing that query a lot over the course of a game.
Or, put a trigger in the task_list that automatically updates the player.player_Points? Is that too much logic to have in the database and should just maintain this relationship in the application?
Thanks.
