1
\$\begingroup\$

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.

Here is the trigger I have created. My question is, 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?

CREATE TRIGGER tr_fight_ResultUpdateFighter
ON fight
FOR INSERT
AS
BEGIN
    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
\$\endgroup\$
3
  • \$\begingroup\$ That is poor design. Weight should be in the fight table as you can fight as different weight. A simple view can sum up wins and losses. This is wrong WHERE fighter_id = _winner_id AND fighter_id = _loser_id. This is not working code. I think it would be a better fit on dba.stackexchange.com. \$\endgroup\$ – paparazzo Dec 11 '16 at 19:20
  • \$\begingroup\$ Don't cross post dba.stackexchange.com/questions/157830/… \$\endgroup\$ – paparazzo Dec 12 '16 at 7:33
  • \$\begingroup\$ Dear, DFraw and NC are worng. you must use OR instead of AND \$\endgroup\$ – Joe Taras Jan 20 '17 at 9:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.