Jump to content

Archived

This topic is now archived and is closed to further replies.

halonine

DB Experts to Comment/Review my DB Structure

Recommended Posts

Hello Everyone!

 

I am a newer (about 6 months) php programmer creating a knowledge base system to use where I work, plus to help me gain valuable programming experience. I have been hacking this together and I have decided I am going to re-write it and release it as an open source project. As I am rewriting it I found my db schema wasn\'t exactly as It should be.

 

I have read several tutorials etc. on DB normalization and I think I now have a db schema that will work. Since I have never worked on a enterprise wide project I really don\'t know what to expect, and I would like some of the DB Design or just plain DB experts to take a look at what I have setup and see if anything jumps out at you as a potential problem, or any other advice you may be able to pass on to me would certainly be appreciated.

 

Here is what I have:


AB_Topic:      

ab_topic_id      mediumint(9) NOT NULL auto_increment

ab_topic_name    tinytext NOT NULL

ab_topic_desc    tinytext NOT NULL

ab_topic_view    tinyint(4) NOT NULL default \'0\'

ab_topic_post    tinyint(4) NOT NULL default \'1\'

ab_userid        int(11) NOT NULL auto_increment    (Topic Moderator)	

       	



AB_Users:        	

ab_userid        int(11) NOT NULL auto_increment

ab_firstname     varchar(25) NOT NULL default \'\'

ab_lastname      varchar(25) NOT NULL default \'\'

ab_email         varchar(25) NOT NULL default \'\'

ab_username      varchar(25) NOT NULL default \'\'

ab_password      varchar(255) NOT NULL default \'\'    (MD5 PW)

ab_userinfo      text NOT NULL

ab_userlevel     enum(\'0\',\'1\',\'2\',\'3\') NOT NULL default \'0\'

ab_signupdate    datetime NOT NULL default \'0000-00-00 00:00:00\'

ab_lastlogin     datetime NOT NULL default \'0000-00-00 00:00:00\'







AB_Search:        	

ab_aid           int(11) NOT NULL default \'0\'

ab_word          varchar(50) NOT NULL default \'\'      

       	

       	

AB_Article:        	

ab_aid           int(11) NOT NULL auto_increment

ab_topic_id      smallint(6) NOT NULL default \'0\'

ab_userid        int(11) NOT NULL default \'0\'

ab_created       date NOT NULL default \'0000-00-00\'

ab_updated       date NOT NULL default \'0000-00-00\'

ab_title         text NOT NULL

ab_shortdesc     tinytext NOT NULL

ab_article       longtext NOT NULL

ab_hits          NOT NULL default \'0\'

ab_article_view  tinyint(4) NOT NULL default \'0\'

 

Only thing I think I may run into a problem with is the search table, it\'s only storing the article ID and the keyword, which dosen\'t exactly meet normallization standards. Would it be better to create another table to make my many-to-many corolation and stop all duplicates from being in the AB_Search table?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.