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

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