You're looking at Less Wrong's discussion board. This includes all posts, including those that haven't been promoted to the front page yet. For more information, see About Less Wrong.

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

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.

Article Navigation

Comments (366)
Tags: meta

Comments (366)

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

Comment author: Luke_A_Somers 04 July 2014 11:11:07AM 4 points [-]

Being fairly ignorant of databases... how would you have laid it out better, in a general sense?

Comment author: lmm 04 July 2014 06:17:28PM *  4 points [-]

Structured tables. One for posts, one for comments, one or more for karma and so on, with appropriately typed columns for each attribute such things have. Alternatively if the data really is unstructured then I'd use a key-value store like Cassandra or something.

(For the record many modern key-value stores didn't exist when the Reddit code was originally written).

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...

Powered by Reddit Powered by Reddit