Crude_Dolorium comments on [moderator action] Eugine_Nier is now banned for mass downvote harassment - Less Wrong

107 Post author: Kaj_Sotala 03 July 2014 12:04PM

You are viewing a comment permalink. View the original post to see all comments and the full post content.

Comments (366)

You are viewing a single comment's thread. Show more comments above.

Comment author: ialdabaoth 04 July 2014 06:43:26PM *  5 points [-]

Seconding this. A proper relational database would look something like this:

CREATE TABLE Users
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(250),
passwordHash VARCHAR(250),
firstname VARCHAR(250),
lastname VARCHAR(250),
description VARCHAR(MAX),
dateCreated DATETIME NOT NULL DEFAULT GETDATE(),
dateLoggedIn DATETIME NOT NULL DEFAULT GETDATE(),
active CHAR(1)
);
CREATE TABLE Themes
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(250),
description VARCHAR(MAX),
css VARCHAR(MAX),
dateCreated DATETIME NOT NULL DEFAULT GETDATE(),
dateEdited DATETIME NOT NULL DEFAULT GETDATE(),
active CHAR(1)
);
CREATE TABLE Forums
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(250),
description VARCHAR(MAX),
users_id_owner INT NOT NULL FOREIGN KEY REFERENCES Users(id),
themes_id INT NOT NULL FOREIGN KEY REFERENCES Themes(id),
dateCreated DATETIME NOT NULL DEFAULT GETDATE(),
dateEdited DATETIME NOT NULL DEFAULT GETDATE(),
active CHAR(1)
);
CREATE TABLE Posts
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
forums_id INT NOT NULL FOREIGN KEY REFERENCES Forums(id),
posts_id_parent INT NOT NULL FOREIGN KEY REFERENCES Posts(id),
users_id_poster INT NOT NULL FOREIGN KEY REFERENCES Users(id),
title VARCHAR(250) NOT NULL,
text VARCHAR(MAX) NOT NULL,
dateCreated DATETIME NOT NULL DEFAULT GETDATE(),
dateEdited DATETIME NOT NULL DEFAULT GETDATE(),
active CHAR(1)
);
CREATE TABLE Votes
(
value INT NOT NULL,
posts_id INT NOT NULL FOREIGN KEY REFERENCES Posts(id),
users_id_voter INT NOT NULL FOREIGN KEY REFERENCES Users(id),
dateCreated DATETIME NOT NULL DEFAULT GETDATE()
);
-- constraint: only one vote per post per user
ALTER TABLE Votes ADD CONSTRAINT pk_Votes PRIMARY KEY (posts_id,user_id)

With that schema, all you'd have to do to see someone's effect on another person's karma is:

SELECT SUM(VALUE) FROM Votes
WHERE users_id_voter = @Voter
AND posts_id IN
(SELECT id FROM Posts WHERE users_id_poster = @User)

EDIT: Wow, formatting is a pain.

Comment author: Crude_Dolorium 16 July 2014 12:40:33AM 5 points [-]

It's heartwarming to see off-the-cuff SQL that includes foreign key constraints.

Comment author: ialdabaoth 16 July 2014 01:46:39AM *  8 points [-]

It's heartwarming to see off-the-cuff SQL that includes foreign key constraints.

Heartwarming enough to offer me a job? ;)

EDIT: Downvoted? Ouch...