Win-Loss Record in SQL Server I'm building a database on Microsoft SQL Server to teach myself SQL. I am a big UFC fan so I'd like to build a database on UFC fighters.

I'm building two tables fight and fighter. The fighter table will have stats like weight, height, birthday, etc. as well as a column for wins, losses, draws, and NC (no contest). The fight table will have a winner_id column and a loser_id column as well as is_draw and is_nc columns.

How can I best create a link between the winner_id/loser_id columns from the fight table to the wins and losses columns for the fighter table?

There is one caveat. Usually MMA fighters will come to the UFC with experience in other organizations. So they will already have a record of wins and losses prior to the UFC. My database will only record UFC fights. So I can't have those previous wins and losses in the [fight] table. For that reason, I've decided to use triggers for now.

Question: is there a better way to do this other than triggers? I'm thinking setting the wins column = a base number + the number of times their fighter_id is listed in the winner_id column. But how would I do that? Would I need to use a view?

Here is the trigger I have created. 

CREATE TRIGGER tr_fight_ResultUpdateFighter
ON fight
FOR INSERT
AS
BEGIN
    -- Win
    DECLARE @winner_id int
    DECLARE @loser_id int
    DECLARE @is_draw bit
    DECLARE @is_nc bit
    SELECT 
        @winner_id = winner_id,
        @loser_id = loser_id,
        @is_draw = is_draw,
        @is_nc = is_nc
        FROM inserted

    --Wins
    UPDATE fighter
    SET wins = wins+1
    WHERE fighter_id = @winner_id

    --Loss
    UPDATE fighter
    SET losses = losses+1
    WHERE fighter_id = @loser_id

    --Draw
    UPDATE fighter
    SET draws = draws + 
        CASE
            WHEN @is_draw = 1
            THEN 1
            ELSE 0
        END
    WHERE fighter_id = @winner_id
    AND fighter_id = @loser_id

    --NC
    UPDATE fighter
    SET nc = nc + 
        CASE
            WHEN @is_nc = 1
            THEN 1
            ELSE 0
        END
    WHERE fighter_id = @winner_id
    AND fighter_id = @loser_id

END
I would start by determining the stats at run-time. Only if that turns out to be too expensive, I would denormalize the statistics:

select f1.fighter_id
     , count(case when f2.is_draw = 0 and f1.fighter_id = f2.winnner_id then 1 end) as #wins
     , count(case when f2.is_draw = 0 and f1.fighter_id = f2.looser_id then 1 end) as #loss
     , count(case when f2.is_draw = 1 and f1.fighter_id in (f2.winner_id, f2.looser_id) then 1 end) as #draws
     , ...   
from fighter f1
join fights  f2
    on f1.fighter_id in (f2.winnner_id, f2.looser_id)
group by f1.fighter_id;