Database design

[TormundTargers]

The database we will need at least these tables:

• Videos
• Users
• Comments

For the first milestone, since we are not implementing commenting, currently we are going to create the Videos (for uploading) and Users (for moderation) tables.

Initial Designs

Videos

Column name	Type	Primary Key	Indexed	Auto Increment	Allow Null	Description
id	integer	Yes	Yes	Yes	No	Auto-incremented unique identifier of a video.
title	varchar(255)	No	Yes	No	No
description	varchar(1000)	No	No	No	Yes
url	varchar(255)	No	No	No	No
filetype	varchar(10)	No	No	No	No
uploader_ip	varchar(15)	No	Yes	No	No
uploader_name	varchar(30)	No	Yes	No	Yes
tripcode	varchar(12)	No	No	No	Yes	10 chars for actual tripcode, extra 2 chars for ! marks.
upload_date	datetime	No	No	No	No
video_status	integer	No	No	No	No	Approved video, unmoderated video or 'deleted forever' video.
removal_code	integer	No	No	No	Yes	It is null if it is not removed.
host_code	integer	No	No	No	No	To help locate and provide support server-side for how to retrieve the video.

Users/Moderators

Column name	Type	Primary Key	Indexed	Auto Increment	Allow Null
id	integer/long	Yes	Yes	Yes	No
name	varchar(30)	No	Yes	No	No
email	varchar(255)	No	No	No	No
password	varchar(255)	No	No	No	No
mod_level	integer	No	No	No	No
last_ip	varchar(15)	No	Yes	No	No

Beware! This is only a first draft.

• Edited to add descriptions.
• Edited to add host_code, change video_status and tripcode

[deltabeard]

Regarding the rows with 'Maybe' in Allow Null:

• I believe description should be allowed to have a NULL stored. Since some videos may not require a description, or an uploader may not want to include a description.
• Removal code should not have NULL stored in it. A code should be given for all removed videos, even if it's a generic error code. EDIT: _removalcode could have NULL stored in it to show that the video has not been removed. Thoughts?
• All administrators should use a name, even when posting public. Moderators and Janitors should not post with a name in public, but their name should be displayed in the moderation page. Therefore I think the name field should not allow NULL to be stored in it, since moderators need to be identified by a name in the moderation interface. Users should be able to see if a moderator or janitor removed a video, but they should not be able to see which specific mod or jan did it. I'm not sure if this is the correct way.

Other changes:

• I think that _uploadername, and _tripcode should allow NULL values, since some users may want to post as anonymous, post with a username but without a trip, post with a trip but without a username, or do both - it should be up to the user.

Other comments:

• Does the length of the _tripcode field (varchar(13)) take in to account the exclamation marks that appear in front of a trip code? On 4chan, a trip has '!' at the start, or '!!' if the trip has been salted.
• What is _videostatus used for?