Building a CMS for College, Advise needed from developers!

stakey

Hi folks,

I am building a content management system in PHP and mySQL for my final year project in college.

I’ve used mySQL and other large relational databases in projects before college and during college but i’m learning PHP for the first time (just to set myself a challenge). This is my first time developing a web application (though I have been doing web design for years and using open source CMS’s like php nuke and what not).

Anyhow, I figured that there must be one or two persons here who have designed and built a CMS for a business customer.

I just wanted some feedback on the database I designed.

The site will allow users to post articles, news, upload files and have discussions via a forum. It’s a three tier system with three different views of the site:

Administrator: View/Add/Edit/Delete Articles, News, Files, Forum Posts, Categories, Users etc…

User: View/Add/Edit Articles, News, Files, Forum Posts

Vistor: View Articles, News, Files, Forum Posts

The database tables I designed to reflect this site are as follows:

** Take into note that News is just a category of Articles

Articles
Article ID PK
Title Varchar
Subtitle Varchar
Author Int FK relates to UserID
Category Int FK relates to Article_Category (Category_ID)
Story Text
Date Posted Timestamp
Comments Int FK relates to Comments (Comment_ID)

Article_Category
Category_ID Int PK
Title Varchar
Description Text

Comments
CommentID Int PK
Title Varchar
Comment Varchar

Users
UserID Int PK
Username Varchar
Password Varchar
UserGroup Int relates to User_Groups (GroupID)
Firstname Varchar
Surname Varchar
Email Varchar
Joined Timestamp

User_Groups
Group ID Int PK
Name Varchar
Permissions Varchar
Description Varchar

Uploads
FileID Int PK
Title Varchar
Author Int FK references Users (UserID)
Cateogry Int FK references Category_Uploads (FileCatID)
Description Text

*** I want to upload the file to the database, should I do it this way or upload it to the server and point the database towards its location?

Category_Uploads
FileCatID Int PK
Name Varchar
Description Text

Forum
PostID Int PK
CategoryID FK reference Category_Forums(CatForumID)
Title Varchar
Author Int FK references Users (UserID)
Post Text
Date Posted Timestamp

Category_Forums
CatForumID Int PK
Name Varchar
Description Text

Forum_Replies
ReplyID Int PK
AuthorID FK References User (UserID)
Comment Text
PostID FK References Forum (PostID)

These were quick solutions to the system as I saw it. If there any improvements folks think I can make then by all means shout. If I’m going totally wrong then please do tell.

Seth Brundle

Seems alright.
re: the uploaded files - this will quickly bloat your database and I would not recommend it unless you really really need them to be secured but something like a .htaccess file may be as good.

The Corinthian

Off the top of hy head:

• Use INT fieldtypes for the timestamps; not as readable without conversion but a lot less hassle to store / convert.
  
• Allow users the option to delete posts that are, say, no more than 10 minutes old.
  
• There seems to be some confusion with regard to your Forum and Forum_Replies tables - I assume these are more correctly Thread and Post tables. If so, the former does not require a timestamp as this is already handled by the posts themselves.
  
• You may want to add a 'ParentID' that references Category_Forums(CatForumID) - so that you can have a forum with sub categories. Root categories will naturally have a ParentID of zero (0). The same can be argued for Article_Category.
  
• You may want a SMALLINT (essentially boolean) field in Articles to denote enabled or visable articles.
  
• As things stand your Articles can only have one comment - for multiple comments the Comments table will have to refrence the Articles table and not vica versa as you currently have.

HTH

stakey

okay i just wrote up the first "draft" of the mysql script for the CMS based on advice taken on board from the nice folks who posted a reply to my initial post.

This is the current state of the tables:

CREATE TABLE core_article
(
article_id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(120) NOT NULL,
subtitle VARCHAR(250) NOT NULL,
author_id INT NOT NULL,
cat_id INT NOT NULL,
body TEXT NOT NULL,
enabled SMALLINT NOT NULL,
timestamp INT NOT NULL,
PRIMARY KEY (article_id),
FOREIGN KEY (author_id) REFERENCES core_users(user_ID),
FOREIGN KEY (cat_id) REFERENCES core_article_category(article_categoryID),
)

CREATE TABLE core_article_category
(
article_categoryID INT NOT NULL AUTO_INCREMENT,
category_name VARCHAR(200),
PRIMARY KEY (article_categoryID)
)

CREATE TABLE core_users
(
user_ID INT NOT NULL AUTO_INCREMENT,
user_name VARCHAR(200) NOT NULL,
user_password VARCHAR(20) NOT NULL,
user_email VARCHAR(100),
user_join INT,
user_class,
PRIMARY KEY (user_ID),
FOREIGN KEY (user_class) REFERENCES core_user_classes(class_id)
)

CREATE TABLE core_user_classes
(
class_id INT NOT NULL AUTO_INCREMENT,
class_name VARCHAR NOT NULL,
PRIMARY KEY (class_id)
)

CREATE TABLE core_comments
(
comment_id NOT NULL AUTO_INCREMENT,
comment_pid INT NOT NULL,
comment_subject NOT NULL,
comment_body TEXT NOT NULL,
comment_author VARCHAR(200) NOT NULL,
comment_author_email VARCHAR(200),
comment_disabled TINYINT(3),
PRIMARY KEY (comment_id),
FOREIGN KEY (comment_pid) REFERENCES core_article(article_id)
)

CREATE TABLE core_upload
(
upload_id NOT NULL AUTO_INCREMENT,
upload_name VARCHAR(200) NOT NULL,
upload_category INT NOT NULL,
upload_description TEXT NOT NULL,
upload_datestamp INT,
PRIMARY KEY (upload_id),
FOREIGN KEY (upload_category) REFERENCES core_upload_cat(upload_catid)
)

CREATE TABLE core_upload_cat
(
uploadcat_id NOT NULL AUTO_INCREMENT,
Uploadcat_name NOT NULL,
PRIMARY KEY (uploadcat_id)
)

CREATE TABLE core_forum_topics
(
topic_id INT NOT NULL AUTO_INCREMENT,
topic_title VARCHAR(150) NOT NULL,
topic_timestamp datetime,
topic_author INT,
PRIMARY KEY (topic_id),
FOREIGN KEY (topic_author) REFERENCES core_users(user_ID)
)

CREATE TABLE core_forum_posts
(
post_id INT NOT NULL AUTO_INCREMENT,
post_tid INT NOT NULL,
post_body TEXT,
post_timestamp datetime,
post_author INT,
PRIMARY KEY (post_id),
FOREIGN KEY (post_author) REFERENCES core_users(user_ID),
FOREIGN KEY (post_tid) REFERENCES core_forum_topics(topic_id)
)

I'm fairly happy with the article/forum/user related tables but im still unsure about the upload feature. I dont want to pack everything into the database i instead want to upload it to a directory on the server. So the current plan is to point the database in the direction of the file location on the server. Any ideas???

If anyone can spot anything out of place by all means let me know.

I was also considering adding a sessions table... opinions, ideas???

cheers

stakey

btw i know there are a few minor spelling mistakes and wrong naming conventions, fixing them now...