Building a Big Project, Part 3

[WingedPanther]

Note: this is part of a series. The first part is here: Building a Big Project, Part 1
The second part is here: Building a Big Project, Part 2

At this point, there will be a temptation to start coding the rest of your site. Unfortunately, we can't do that. We've reached the point where we simply have to have a database to work with.

There are a few reasons for this. First of all, I like to slowly develop functionality, and have each item be fully functional as I develop it. Because the nature of the site is going to be highly dependent on the database, we need to get it created now.

One of the other temptations you may have is to ask me what database management program I'll recommend. After all, with all the programs I've listed so far, surely I have something fantastic to recommend, right? Wrong! Use the command line. Most databases are designed to have users interact with them through SQL. Do so. This section is going to be about how to design your database, and how to build it from scratch.

If you downloaded the zip file in Part 1, you will have seen a file called DB-Structure5.mdpf. You can open it with Dynamic Draw and you'll see a fairly complete list of all the tables and fields for the database.

The three major tables are USERS, STORIES, and CHAPTERS. We have to have users and stories to have a fanfiction site. In order to keep stories manageable, we'll break them into chapters.

USERS will have the most fields of any table. PENNAME will be our primary key. As the primary key, it cannot be null. I've decided that 30 characters is long enough for it. EMAIL needs to be longer: about 100 characters. It will be used for notifications to the user. One thing that fanfiction.net stopped doing a while ago was allowing adult content. My guess is they wanted to avoid liability problems. I think sites like DeviantArt.com handle it better. If you're old enough, you can select to view or not view adult content. To handle that, we need BIRTHDATE to be a date field. PASSWORD will store the SHA1 hash of the user's password. As a result, it will need to be 40 characters. CONFIRMATION will store the confirmation code sent in the welcome email when a user registers. It will be 20 characters. Continuing like this will give us all the fields for USERS.

Building our script to create the database, we get this:

create database myfiction;
use myfiction;

create table USERS
(
PENNAME VARCHAR(30) NOT NULL,
EMAIL VARCHAR(100),
BIRTHDATE DATE,
PASSWORD VARCHAR(50),
CONFIRMATION VARCHAR(20),
WEBSITE VARCHAR(100),
SHOWEMAIL BOOL,
COUNTRY INT,
SHOWCOUNTRY BOOL,
ALLOWPM BOOL,
PROFILE TEXT,
AVATARNAME VARCHAR(20),
AVATARBLOB BLOB,
BETASTATUS BOOL,
BETAPROFILE TEXT,
BETARATING TINYINT,
ISMODERATOR BOOL,
ISADMIN BOOL,
LASTIP VARCHAR(20),
PRIMARY KEY (PENNAME)
);

create table STORIES
(
STORY_GUID INT,
TITLE VARCHAR(100) NOT NULL,
DESCRIPTION TEXT,
USE_CHAPTERS BOOL,
COMPLETE BOOL,
STORY_RATING INT,
STAR_GIVERS INT,
TOTAL_STARS INT,
ENABLED BOOL,
PRIMARY KEY (STORY_GUID)
)

create table CHAPTERS
(
STORY_GUID INT,
CHAPTER INTEGER,
CHAPTER_TITLE VARCHAR(100),
BODY TEXT,
WORD_COUNT INT,
PRIMARY KEY (STORY_GUID,CHAPTER)
);

Along with needing the basic content, we will need to manage our users and their interactions. For this section, I'm actually borrowing heavily from my experience as a moderator here at CodeCall. Any community needs to have a rules, and a way to enforce them. As a result, users can receive infractions for violating the rules, and users can report stories or private messages that violate those rules.

Of course, to report private messages, we have to be able to store and send them. In addition, we need to be able to ban known sources of spam. We can do that by email and by IP. Notice something else important: if you look at some of these tables, such as PM, you should be able to easily tell what functionality is intended.

create table USERINFRACTIONS
(
INFRACT_GUID INT,
PENNAME VARCHAR(30) NOT NULL,
DATE_GIVEN DATE,
GIVEN_BY VARCHAR(30) NOT NULL,
POINTS INT,
EXPIRES DATE,
REASON TEXT,
ISWARNING BOOL,
PRIMARY KEY (INFRACT_GUID)
);

create table STORYREPORT
(
REPORT_GUID INT,
STORY_GUID INT,
CHAPTER INT,
REASON TEXT,
SUBMITTED_BY VARCHAR(30),
PRIMARY KEY (REPORT_GUID)
);

create table PMREPORT
{
REPORT_GUID INT,
PM_GUID INT,
REASON TEXT,
SUBMITTED_BY VARCHAR(30),
PRIMARY KEY (REPORT_GUID)
);

create table PM
(
PM_GUID INT,
PM_CONTENT TEXT,
FROMUSER VARCHAR(30),
TOUSER VARCHAR(30),
TIME DATETIME,
ISREAD BOOL,
DELETEDBYTO BOOL,
DELETEDBYFROM BOOL,
PRIMARY KEY (PM_GUID)
);

create table BANNEDIPS
(
IPADDRESS VARCHAR(20),
PRIMARY KEY (IPADDRESS)
);

create table BANNEDEMAILS
(
EMAILADDRESS VARCHAR(100),
PRIMARY KEY (EMAILADDRESS)
);

If you think about the nature of fiction, you will quickly realize that it frequently has a few common elements. It will have one or more genres, characters, etc. When dealing with fanfiction, this becomes more important. The following tables provide lists of all the relevant information. Categories includes things like "Animated", "Comic/Manga", "Movie", etc. Genres include things like "Horror", "Humor", "Romance", etc. Ratings range from "E" to "NC17"

create table CATEGORIES
(
CATEGORY VARCHAR(30) NOT NULL,
PRIMARY KEY (CATEGORY)
);

create table GENRES
(
GENRE VARCHAR(30) NOT NULL,
PRIMARY KEY (GENRE)
);

create table LANGUAGES
(
LANGUAGE VARCHAR(30) NOT NULL,
PRIMARY KEY (LANGUAGE)
);

create table FANDOMS
(
FANDOM VARCHAR(100) NOT NULL,
PRIMARY KEY (FANDOM)
);

create table CHARACTERS
(
FANDOM VARCHAR(100) NOT NULL,
FANCHAR VARCHAR(100) NOT NULL,
PRIMARY KEY (FANDOM, FANCHAR)
);

create table RATINGS
(
RATING_GUID INT,
RATING_TITLE VARCHAR(30) NOT NULL,
PRIMARY KEY (RATING_GUID)
);

The remaining tables are used to hook lists to stories/users. To support writers who are learning the craft, users can volunteer to be a beta-reader. This allows more experienced writers to help along those less experienced. If you're a Naruto fan, however, you may not be interested in receiving a Romance Fic based on the soulful interaction of the characters from Gossip Girl finally hooking up! The following items let beta-readers indicate the stories they're into.

create table BETACATS
(
PENNAME VARCHAR(30) NOT NULL,
CATEGORY VARCHAR(30) NOT NULL,
PRIMARY KEY (PENNAME, CATEGORY)
);

create table BETAGENRES
(
PENNAME VARCHAR(30) NOT NULL,
GENRE VARCHAR(30) NOT NULL,
PRIMARY KEY (PENNAME, GENRE)
);

create table BETAFANDOMS
(
PENNAME VARCHAR(30) NOT NULL,
FANDOM VARCHAR(100) NOT NULL,
PRIMARY KEY (PENNAME, FANDOM)
);

Finally, we have the tables that glue everything together. The reason I've created these tables is to directly address a variety of problems that can come up. FANDOMCATS: "The Crow" is a series of movies, comics, and novels. I want people to be able to find the fanfictions through any of these routes when browsing. STORYAUTHORS: I cowrote a story with Sybil a while back. We couldn't both claim credit for it. She did most of the writing, so I made her post it However, why can't we both take credit for it? STORYBETAS: If you've received a lot of help from a beta-reader, why not give him/her credit? If you're a beta-reader and the moron didn't take your advice, why accept the blame? STORYFANDOMS: Sybil has written crossovers between 3 fandoms. She should be searchable in all three. STORYCHARSxxx: Fanfiction.net only lets you mark two characters. What if you are using three, or five, or all? What if you are focusing on three as major and five as minor?

create table FANDOMCATS
(
FANDOM VARCHAR(100) NOT NULL,
CATEGORY VARCHAR(30) NOT NULL,
PRIMARY KEY (FANDOM, CATEGORY)
);

create table STORYAUTHORS
(
STORY_GUID INT,
PENNAME VARCHAR(30) NOT NULL,
CONFIRMED BOOL,
SUBMITTER BOOL,
PRIMARY KEY (STORY_GUID, PENNAME)
);

create table STORYBETAS
(
STORY_GUID INT,
PENNAME VARCHAR(30) NOT NULL,
CONFIRMED BOOL,
PRIMARY KEY (STORY_GUID, PENNAME)
);

create table STORYCATS
(
STORY_GUID INT,
CATEGORY VARCHAR(30) NOT NULL,
PRIMARY KEY (STORY_GUID, CATEGORY)
);

create table STORYGENRES
(
STORY_GUID INT,
GENRE VARCHAR(30) NOT NULL,
PRIMARY KEY (STORY_GUID, GENRE)
);

create table STORYFANDOMS
(
STORY_GUID INT,
FANDOM VARCHAR(100) NOT NULL,
PRIMARY KEY (STORY_GUID, FANDOM)
);

create table STORYCHARSMAJOR
(
STORY_GUID INT,
FANDOM VARCHAR(100) NOT NULL,
FANCHAR VARCHAR(100) NOT NULL,
PRIMARY KEY (STORY_GUID, FANDOM, FANCHAR)
);

create table STORYCHARSMINOR
(
STORY_GUID INT,
FANDOM VARCHAR(100) NOT NULL,
FANCHAR VARCHAR(100) NOT NULL,
PRIMARY KEY (STORY_GUID, FANDOM,FANCHAR)
);

Some closing comments. First of all, this is a work in progress! Don't let your database design stop you from adding a feature. By the same token, if you have a set of features you intend to add, your design will reflect it. The linking tables you see here are a direct reflection of a design decision. Can you imagine the coding nightmare that would exist if I wasn't planning this in advance, however?

Second: you'll notice that I haven't used any foreign keys. That is not an oversight. I have worked in databases both with and without them, and have found that they can make the logic of your code far more tortured. Yes, I can easily get orphaned records without them, but I think it's worth the risk.

Finally, this does not reflect any form of optimization. Triggers, indexes, stored procedures, views, and various other database techniques are not presented here. Security has also not been dealt with. The only thing that should be obvious, is I am not attempting to acheive security through obscurity. As someone who does database migrations, I can assure you that security through obscurity does not work. It can irritate people, but it doesn't stop them.

[Jordan]

Reading other database schemes is really interesting. You can kind of see how a person logically thinks and how they link data. In case you are curious, here is how the vBulletin infraction table is structured:

[highlight=sql]CREATE TABLE IF NOT EXISTS `infraction` (
`infractionid` int(10) unsigned NOT NULL auto_increment,
`infractionlevelid` int(10) unsigned NOT NULL default '0',
`postid` int(10) unsigned NOT NULL default '0',
`userid` int(10) unsigned NOT NULL default '0',
`whoadded` int(10) unsigned NOT NULL default '0',
`points` int(10) unsigned NOT NULL default '0',
`dateline` int(10) unsigned NOT NULL default '0',
`note` varchar(255) NOT NULL default '',
`action` smallint(5) unsigned NOT NULL default '0',
`actiondateline` int(10) unsigned NOT NULL default '0',
`actionuserid` int(10) unsigned NOT NULL default '0',
`actionreason` varchar(255) NOT NULL default '',
`expires` int(10) unsigned NOT NULL default '0',
`threadid` int(10) unsigned NOT NULL default '0',
`customreason` varchar(255) NOT NULL default '',
PRIMARY KEY (`infractionid`),
KEY `expires` (`expires`,`action`),
KEY `userid` (`userid`,`action`),
KEY `infractonlevelid` (`infractionlevelid`),
KEY `postid` (`postid`),
KEY `threadid` (`threadid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=336 ;[/highlight]

There is also infractionban, infractiongroup and infractionlevel tables.

[BlaineSch]

Hey, WP, I got a question.

So I see your table, and for my standards that is really high. I have played around with an idea for a while but something like a "userdata" table. Where basically it has the data name, the data value, the user it links too, and that auto id, right? Would this be effective at all?

I am still reading so I may comment again in a bit..

[WingedPanther]

@Jordan: Currently, the infraction system I have in mind is fairly nebulous. I know I will be altering the database structure as I move forward. Currently, the idea is that a user receives an infraction, not a post or PM or other object, and that they are automatically cleaned up on the expiration date.

The tables listed above are already more than originally planned for!

@Blaine: That sounds pretty reasonable. I've seen something similar in the past, for having "unlimited" data fields.

[amrosama]

one tiny question:
why did you choose SHA1 hash over md5 or any other hashing algorithm?

[WingedPanther]

Actually, I end up using SHA2-512. It's far more secure than MD5