Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am creating a database for a very basic website. I have created the tables and put what I thought might be the correct schema, however I am having doubts about it as well as a couple problems. All I am really looking for here is a simple yes or no as to whether or not this is normalized and a gentle push in the right direction to fixing it.

I am looking to have a person create a profile, make multiple comments on 1 or more articles on a given page as well as house the various articles inside the database.

Thank you for your time folks.

My tables are as follows:

USERS (userID, fname, lname, email, username, pwd, admin)

COMMENT (commentID, userID, infoID, comment)

INFO (infoID, asset, description)

PAGE (pageID, name)

PAGE_INFO (pageID, infoID)

ADMIN (userID)

CREATE TABLE users ( users_id int(11) NOT NULL AUTO_INCREMENT, fname varchar(45) DEFAULT NULL, lname varchar(45) DEFAULT NULL, email varchar(45) DEFAULT NULL, username varchar(45) DEFAULT NULL, pwd varchar(45) DEFAULT NULL, admin tinyint(2) DEFAULT 1, PRIMARY KEY (users_id), UNIQUE KEY username (username) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE comment ( comment_id int(11) NOT NULL AUTO_INCREMENT, username varchar(45) NOT NULL, info_id int(11) NOT NULL DEFAULT 0, comment varchar(140) NOT NULL, PRIMARY KEY (comment_id), KEY fk_comment_username1_idx (username), KEY fk_comment_info1_idx (info_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE info ( info_id int(11) NOT NULL AUTO_INCREMENT, asset varchar(255) DEFAULT NULL, description varchar(25) DEFAULT NULL, PRIMARY KEY (info_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE page ( page_id int(11) NOT NULL AUTO_INCREMENT, name varchar(45) DEFAULT NULL, PRIMARY KEY (page_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE page_info ( page_id int(11), info_id int(11), KEY fk_page_info_page1_idx (page_id), KEY fk_page_info_info1_idx (info_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE admin ( user_id int(11), PRIMARY KEY (user_id), KEY fk_admin_users1_idx (user_id), ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

share|improve this question
    
You seem to have a nice schema there. As a first look, my concerns would be (minor) that you don't need the user(admin) flag since you have the table admin for that. Second (major concern) would be why you are not using InnoDB, if you want to enforce integrity. –  ypercube May 6 at 17:58
    
@ypercube I never noticed before that I wasn't using InnoDB. Also, I have the admin table for future modifications. Right now I am using the user(admin) as a check against my session. In my mind it just seems easier that way. I will eventually just delete the user(admin) and just use the admin table later. –  krejar May 6 at 18:27
    
Why is the comment field in the comment table default to NULL? –  JeffO May 6 at 19:16
    
@JeffO I used mysql WB to create this using their table wizard. I don't remember setting the default to NULL in comment(comment). I've changed it since. I will reflect the above code to show momentarily. –  krejar May 6 at 20:53
    
page.page_id is missing the auto_increment; admin tinyint(2) could also be tinyint(1) or BIT; a comment with only 140 chars could be to less? I am not sure why you use for the names only varchar 45 (or info.description only varchar 25), I am afraid this could be too small. –  da_didi May 7 at 8:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.