Rock, Paper, Scissors, Lizard and Spock - Relational Paradigm

Question

I found a few question about the game and IMHO it's a relational problem and better addressed by set logic. Thus I created this solution in SQL to get away from ugly JavaScript, elegant java and practical C#

--drop table [Hand]
--drop table [Action]

create table [Hand]
(
     Id int primary key not null
    ,Name varchar(20) not null
)

create table [Action]
(
     WinsId int not null
    ,LosesId int not null
    ,ActionText varchar(100) not null
)
GO

alter table [Action] with nocheck add constraint fk_ActionhandWins foreign key(WinsId) references [Hand] (Id)
alter table [Action] with nocheck add constraint fk_ActionhandLoses foreign key(LosesId) references [Hand] (Id)
GO

insert into [Hand] (Id, Name)
values
 (1, 'Rock')
,(2, 'Paper')
,(3, 'Scissors')
,(4, 'Lizard')
,(5, 'Spock')

insert into [Action] (winsId, ActionText, LosesId)
values
  (1, 'Crushes', 3)
 ,(1, 'Crushes', 4)
 ,(2, 'Covers', 1)
 ,(2, 'Disproves', 5)
 ,(3, 'Cuts', 2)
 ,(3, 'Beheads', 4)
 ,(4, 'Eats', 2)
 ,(4, 'Poisons', 5)
 ,(5, 'Vaporizes', 1)
 ,(5, 'Distrois', 3)

 GO

 -- All possiblities
 select w.Name + ' ' + a.ActionText + ' ' + l.Name
 from [Action] a
 join [Hand] w on w.Id = a.WinsId
 join [Hand] l on l.Id = a.LosesId

GO
--drop function dbo.RockPaperScissorsLizardSpock
create function dbo.RockPaperScissorsLizardSpock(@Player1 int, @Player2 int)
returns @Result table(Result varchar(200), [Text] varchar(200))
as begin

    insert into @Result
             select 'Player 1 Wins!' as Result, w.Name + ' ' + a.ActionText + ' ' + l.Name as [Text]
             from [Action] a
             join [Hand] w on w.Id = a.WinsId
             join [Hand] l on l.Id = a.LosesId
             where w.Id = @Player1
             and l.Id = @Player2
             UNION
             select 'Player 2 Wins!' as Result, w.Name + ' ' + a.ActionText + ' ' + l.Name as [Text]
             from [Action] a
             join [Hand] w on w.Id = a.WinsId
             join [Hand] l on l.Id = a.LosesId
             where w.Id = @Player2
             and l.Id = @Player1

    if not exists(select * from @Result)
    insert into @Result values ('Tie!','')

    return

end;
GO
-- Testing cases
select * from dbo.RockPaperScissorsLizardSpock(1,2)
select * from dbo.RockPaperScissorsLizardSpock(2,1)
select * from dbo.RockPaperScissorsLizardSpock(1,1)
GO

Advantages of this approach are it doesn't require any implementation to add more "hands" just keep populating the two tables.

Are there any disadvantages of this approach compared to using OOP?

Answer 1

Validate actions

The Action table could use some validation logic: WinsId and LosesId should be unique together. You could enforce this with a unique constraint.

But there's more for the game to make sense:

• WinsId should not be equal to LosesId
• Each WinsId and LosesId should have the same count. That is, each action should beat and be beaten by the same number of other actions.

Although these conditions are not possible to enforce with constraints, it would be good to add a helper function that can be used to perform a sanity check easily.

Handling ties

Instead of this:

INSERT INTO @Result
SELECT 'Player 1 Wins!', ...
UNION
SELECT 'Player 2 Wins!', ...

IF NOT EXISTS(SELECT * FROM @Result)
INSERT INTO @Result VALUES ('Tie!','')

I don't have an SQL Server with me to test, but I think this simpler alternative or similar should work:

INSERT INTO @Result
SELECT 'Player 1 Wins!', ...
UNION
SELECT 'Player 2 Wins!', ...
UNION
SELECT 'Tie', '' WHERE @Player1 = @Player2

Highlight SQL keywords

The common practice is to capitalize SQL keywords, like this:

CREATE TABLE [Hand]
(
     Id INT PRIMARY KEY NOT NULL
    ,Name VARCHAR(20) NOT NULL
)

Use indentation effectively

Good indentation goes a long way to helping readers understand the code. A block of code like this, overindented and practically flat at the same, is not really helpful:

insert into @Result
         select 'Player 1 Wins!' as Result, w.Name + ' ' + a.ActionText + ' ' + l.Name as [Text]
         from [Action] a
         join [Hand] w on w.Id = a.WinsId
         join [Hand] l on l.Id = a.LosesId
         where w.Id = @Player1
         and l.Id = @Player2
         UNION
         select 'Player 2 Wins!' as Result, w.Name + ' ' + a.ActionText + ' ' + l.Name as [Text]
         from [Action] a
         join [Hand] w on w.Id = a.WinsId
         join [Hand] l on l.Id = a.LosesId
         where w.Id = @Player2
         and l.Id = @Player1

It would be better this way:

INSERT INTO @Result
SELECT 'Player 1 Wins!' AS Result, w.Name + ' ' + a.ActionText + ' ' + l.Name AS [Text]
    FROM [Action] a
        JOIN [Hand] w ON w.Id = a.WinsId
        JOIN [Hand] l ON l.Id = a.LosesId
    WHERE w.Id = @Player1 AND l.Id = @Player2
UNION
SELECT 'Player 2 Wins!' AS Result, w.Name + ' ' + a.ActionText + ' ' + l.Name AS [Text]
    FROM [Action] a
        JOIN [Hand] w ON w.Id = a.WinsId
        JOIN [Hand] l ON l.Id = a.LosesId
    WHERE w.Id = @Player2 AND l.Id = @Player1

Naming

Single letter table aliases like w, l, a are not very helpful. It would help understanding this code if you spelled out winner, loser, action.