Luke_A_Somers comments on [moderator action] Eugine_Nier is now banned for mass downvote harassment

[Luke_A_Somers]

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

[lmm]

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

[ialdabaoth]

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.

[Crude_Dolorium]

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

[ialdabaoth]

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

Heartwarming enough to offer me a job? ;)

EDIT: Downvoted? Ouch...