MafiaScum Game Database

[Firebringer]

I kind of want to create a database for mafiascum completed games for each of the different queues of the site
I hope to do this so other people and myself can gather analysis on various things that are happening in the those segments.
For instance replacement pace, game length, victory trends, role appearances and alignment ties, and various other probably interesting things. Maybe even figure out how many games a player plays at a time with a simple query.

I am wondering if anyone actually wants this, since I kind of want to do it but I don't know if i will have time to do it.

If no, disregard.

If yes, tell me what kind of things you want in this kind of database.

[Vi]

A starting point that people haven't been updating recently is the List of Large Themes page along with others for other queues.

You want a lot more than that and that's totally work but I'm letting you know it's there.

[Firebringer]

A starting point that people haven't been updating recently is the List of Large Themes page along with others for other queues.

You want a lot more than that and that's totally work but I'm letting you know it's there.

Thanks, these would be good starting point.

[mastin2]

Not sure how up-to-date it's kept, but we

do

have a records page as well.

[callforjudgement]

Ideally we should set up something that can be crowd-sourced. One person entering details on every game would be very tedious, but a bunch of people working together should be able to complete a game database fairly quickly.

The information I'd put in such a database would be the identity the mod, closed/open (and if open, the name of the setup if it has one), the list of players who held each slot (including the player who held the slot when it died / the game ended), the role and alignment of the slot, and how/when/whether it died, and the game result. For a Nightless, I'd give the Mafia a different faction name that reflected their lack of kill (sort-of like Aliens), as that's an easier form for analysis. This is the information that's normally placed in the OP of a game.

[Ircher]

Looking at players wiki pages is probably a good start.

[zMuffinMan]

so i started doing something similar to this about a week ago

because i was bored one afternoon

because i thought it might be useful in some way

i created a database mostly for keeping track of games i'm in (haven't done anything with other games yet) and it was primarily to deal with looking at a player's vote history and generating vote counts but since i didn't really know what i wanted to do with it, i created tables in such a way that i'd be able to pretty much do anything with them

i plan to create some sort of front-end thing for easy inserts/updates/output, but haven't had time in the last week to actually do it yet

here's what my current table structures look like, for reference:

Spoiler:

Code: Select all

IF OBJECT_ID('tblUsers', 'U') IS NOT NULL DROP TABLE tblUsers; /* records all user IDs, names and join dates (join dates technically optional). */
CREATE TABLE tblUsers (
	  userID INT PRIMARY KEY
	, userName VARCHAR(255) NOT NULL
	, joinDateUTC DATETIME);
CREATE UNIQUE INDEX IX_Users_userName ON tblUsers (userName);
CREATE INDEX IX_Users_joinDate ON tblUsers (joinDateUTC);
GRANT INSERT, UPDATE, DELETE, SELECT ON tblUsers TO tCIT;
GO

IF OBJECT_ID('tblUserAlts', 'U') IS NOT NULL DROP TABLE tblUserAlts; /* records all known user alts, including hydras. for looking up a particular player */
CREATE TABLE tblUserAlts (
	  userMain INT
	, userAlt INT
	, PRIMARY KEY (userMain, userAlt));
GRANT INSERT, UPDATE, DELETE, SELECT ON tblUserAlts TO tCIT;
GO

IF OBJECT_ID('tblGameCategories', 'U') IS NOT NULL DROP TABLE tblGameCategories; /* themes, normals, minis, larges, etc */
CREATE TABLE tblGameCategories (
	  gameCatID INT IDENTITY(1, 1) PRIMARY KEY
	, catName VARCHAR(255) NOT NULL
	, catLocation VARCHAR(255) NOT NULL
	, catDescription VARCHAR(255)
	, CONSTRAINT UQ_GameCategories_catName UNIQUE (catName));
GRANT INSERT, UPDATE, DELETE, SELECT ON tblGameCategories TO tCIT;
GO

INSERT tblGameCategories (catName, catLocation, catDescription)
VALUES ('Newbie', 'Road to Rome', 'Games designed to ease newbie players (like Cupcake) into the how the game is played here. Historically, nine-player games.')
	 , ('Open', 'Central Park', 'Games where the setup is open knowledge. Can be any size.')
	 , ('Micro', 'Mayfair Club', 'Games for nine players or fewer. Can be normal games or theme games.')
	 , ('Mini Normal', 'Little Italy', 'Games for between ten and thirteen players. Must conform to normal guidelines.')
	 , ('Mini Theme', 'Coney Island', 'Games for between ten and thirteen players. Games may include flavour and non-normal mechanics (and are more fun than normal games).')
	 , ('Large Normal', 'New York', 'Games for fourteen or more players. Must conform to normal guidelines.')
	 , ('Large Theme', 'Theme Park', 'Games for fourteen or more players. Games may include flavour and non-normal mechanics (and are more fun than normal games).')
	 , ('Marathon', 'Blitz Game Trials', 'Rapid games, usually lasting at most one or two hours.')
	 , ('Blitz', 'Marathon Forum', 'Quick (which means not as quick as rapid) games, with deadlines no longer than four days.');
GO

IF OBJECT_ID('tblGameTypes', 'U') IS NOT NULL DROP TABLE tblGameTypes; /* open, closed, semi-open... other? */
CREATE TABLE tblGameTypes (
	  gameTypeID INT IDENTITY(1, 1) PRIMARY KEY
	, typeName VARCHAR(255) NOT NULL
	, typeDescription VARCHAR(255)
	, CONSTRAINT UQ_GameTypes_typeName UNIQUE (typeName));
GRANT INSERT, UPDATE, DELETE, SELECT ON tblGameTypes TO tCIT;
GO

INSERT tblGameTypes (typeName, typeDescription)
VALUES ('Closed', 'No roles in a game are public knowledge')
	 , ('Open', 'All roles in a game are public knowledge')
	 , ('Semi-Open', 'All possible roles in a game are public knowledge, but which roles exist in the game aren''t')
	 , ('Quasi-Open', 'Only some roles in the game are public knowledge');
GO

IF OBJECT_ID('tblGames', 'U') IS NOT NULL DROP TABLE tblGames; /* record of all games, including moderator, category, type, size, name and when it started */
CREATE TABLE tblGames (
	  gameID INT PRIMARY KEY
	, gameModerator INT NOT NULL
	, gameCatID INT NOT NULL
	, gameTypeID INT NOT NULL
	, gameSize INT NOT NULL
	, gameName VARCHAR(255) NOT NULL
	, dayStartVotecount BIT NOT NULL DEFAULT(0) /* whether a game has a votecount at the start of a day phase with all living players... determines default value of "votecounts" in tblGameVotingHistory */
	, gameCreatedUTC DATETIME
	, gameStartUTC DATETIME);
CREATE INDEX IX_Games_gameModerator ON tblGames (gameModerator) INCLUDE (gameCatID, gameTypeID, gameSize, gameName, gameCreatedUTC);
CREATE INDEX IX_Games_gameCatID ON tblGames (gameCatID) INCLUDE (gameModerator, gameSize, gameTypeID);
CREATE INDEX IX_Games_gameTypeID ON tblGames (gameTypeID) INCLUDE (gameModerator, gameSize, gameCatID);
GRANT INSERT, UPDATE, DELETE, SELECT ON tblGames TO tCIT;
GO

IF OBJECT_ID('tblAlignments', 'U') IS NOT NULL DROP TABLE tblAlignments; /* scum, town, third parties */
CREATE TABLE tblAlignments (
	  alignmentID INT IDENTITY(1, 1) PRIMARY KEY
	, alignmentName VARCHAR(255) NOT NULL
	, alignmentDescription VARCHAR(255));
GRANT INSERT, UPDATE, DELETE, SELECT ON tblAlignments TO tCIT;
GO

INSERT tblAlignments (alignmentName, alignmentDescription)
VALUES ('Town', 'zMuffinMan')
	 , ('Mafia (Primary)', 'The first (or only) mafia team in a game');
GO

IF OBJECT_ID('tblRoles', 'U') IS NOT NULL DROP TABLE tblRoles; /* roles... could get messy given the same role names aren't necessarily the same thing but eh */
CREATE TABLE tblRoles (
	  roleID INT IDENTITY(1, 1) PRIMARY KEY
	, roleName VARCHAR(255)
	, roleDescription VARCHAR(4000));
CREATE INDEX IX_Roles_roleName ON tblRoles (roleName);
GRANT INSERT, UPDATE, DELETE, SELECT ON tblRoles TO tCIT;
GO

IF OBJECT_ID('tblPlayerSlots', 'U') IS NOT NULL DROP TABLE tblPlayerSlots; /* creating a game in tblGames should create a record here based on game size */
CREATE TABLE tblPlayerSlots (
	  playerSlot INT
	, gameID INT
	, alignmentID INT /* update after flips */
	, roleID INT /* update after flips */
	, PRIMARY KEY (playerSlot, gameID));
GRANT INSERT, UPDATE, DELETE, SELECT ON tblPlayerSlots TO tCIT;
GO

IF OBJECT_ID('tblPlayers', 'U') IS NOT NULL DROP TABLE tblPlayers; /* a list of players in the game and what slot they're in */
CREATE TABLE tblPlayers (
	  gameID INT NOT NULL
	, userID INT NOT NULL
	, playerSlot INT NOT NULL
	, dateEnteredUTC DATETIME
	, PRIMARY KEY (userID, gameID));
CREATE INDEX IX_Players_gameID ON tblPlayers (gameID);
CREATE INDEX IX_Players_userID ON tblPlayers (userID);
CREATE INDEX IX_Players_playerSlot ON tblPlayers (gameID, playerSlot, dateEnteredUTC);
GRANT INSERT, UPDATE, DELETE, SELECT ON tblPlayers TO tCIT;
GO

IF OBJECT_ID('tblGameCycles', 'U') IS NOT NULL DROP TABLE tblGameCycles; /* day and night phases count as a single cycle, but a game may not have both a day and night phase in a single cycle... */
CREATE TABLE tblGameCycles (
	  gameID INT
	, gameCycle INT
	, gameDay BIT NOT NULL DEFAULT(0)
	, gameDayStart DATETIME
	, gameNight BIT NOT NULL DEFAULT(0)
	, gameNightStart DATETIME
	, votecounts INT NOT NULL DEFAULT(0) /* how many votecounts have been produced for a game */
	, dayDeadline VARCHAR(255) /* 'XXX' in '[countdown]XXX[/countdown]' */
	, PRIMARY KEY (gameID, gameCycle));
GRANT INSERT, UPDATE, DELETE, SELECT ON tblGameCycles TO tCIT;
GO

IF OBJECT_ID('tblGameVotingHistory', 'U') IS NOT NULL DROP TABLE tblGameVotingHistory; /* records votes of all players in a game */
CREATE TABLE tblGameVotingHistory (
	  ID INT IDENTITY(1, 1) PRIMARY KEY
	, gameID INT NOT NULL
	, playerVoting INT NOT NULL
	, playerVoted INT NOT NULL
	, postVoted INT NOT NULL
	, gameCycle INT NOT NULL
	, dateVotedUTC DATETIME);
CREATE INDEX IX_GameVotingHistory_gameID ON tblGameVotingHistory (gameID) INCLUDE (playerVoting, playerVoted, gameCycle, dateVotedUTC);
GRANT INSERT, UPDATE, DELETE, SELECT ON tblGameVotingHistory TO tCIT;
GO

IF OBJECT_ID('tblGameDeaths', 'U') IS NOT NULL DROP TABLE tblGameDeaths; /* self-explanatory, but records what phase they died in */
CREATE TABLE tblGameDeaths (
	  gameID INT
	, nightKilled BIT NOT NULL
	, lynched BIT NOT NULL
	, gameCycle INT NOT NULL
	, playerSlot INT
	, dateOfDeath DATETIME
	, PRIMARY KEY (gameID, playerSlot));
GRANT INSERT, UPDATE, DELETE, SELECT ON tblGameDeaths TO tCIT;
GO 

this is an example of a proc to spit out vote history

Spoiler: vote history

Code: Select all

CREATE PROC usp_spitOutVoteHistory
	  @gameID INT
	, @playerSlot INT = NULL
	, @playerName VARCHAR(255) = NULL /* realistically, the procedure should only be taking a playerSlot as a parameter and not a playerName, but since I'm doing it manually, I don't really care atm */
	, @output VARCHAR(MAX)
AS BEGIN 
	SELECT @output += '[table][header]Player[/header][header]Vote[/header][header]Date of Vote (UTC)[/header][header]Post Voted[/header]';

	SELECT @output += '[row][cell]' + Voting.userName + '[/cell][cell]' 
		 + Voted.userName + '[/cell][cell]' + CONVERT(NVARCHAR(255), dateVotedUTC, 113) + '[/cell][cell][post=' 
		 + CAST(GH.postVoted AS VARCHAR(255)) + ']' + CAST(GH.postVoted AS VARCHAR(255)) + '[/post][/cell][/row]'
	FROM tblGameVotingHistory GH
	CROSS APPLY (
		SELECT userName
		FROM tblUsers U
		JOIN (
			SELECT gameID, userID, playerSlot, dateEnteredUTC, ROW_NUMBER() OVER (PARTITION BY gameid, playerSlot ORDER BY dateEnteredUTC DESC) RN
			FROM tblPlayers
			WHERE dateEnteredUTC < GH.dateVotedUTC) P ON P.userID = U.userID
		WHERE P.playerSlot = GH.playerVoting
		AND P.gameID = GH.gameID
		AND P.RN = 1) Voting
	CROSS APPLY (
		SELECT userName
		FROM tblUsers U
		JOIN (
			SELECT gameID, userID, playerSlot, dateEnteredUTC, ROW_NUMBER() OVER (PARTITION BY gameid, playerSlot ORDER BY dateEnteredUTC DESC) RN
			FROM tblPlayers
			WHERE dateEnteredUTC < GH.dateVotedUTC) P ON P.userID = U.userID
		WHERE P.playerSlot = GH.playerVoted
		AND P.gameID = GH.gameID
		AND P.RN = 1) Voted
	WHERE GH.gameID = @gameID
	AND GH.playerVoting = COALESCE(@playerSlot, GH.playerVoting)
	AND GH.playerVoting = COALESCE((SELECT playerSlot FROM tblPlayers P JOIN tblUsers U ON U.userID = P.userID WHERE U.userName LIKE @playerName + '%' AND P.gameID = @gameID), GH.playerVoting);

	SELECT @output += '[/table]';
END
GO

theoretically, if i didn't care about voting history, i could just use the other tables to spit out different stats. there's prob also a bunch of ways i could improve this but don't really care since it works for my needs