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_incrementab_topic_name tinytext NOT NULLab_topic_desc tinytext NOT NULLab_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_incrementab_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 NULLab_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_incrementab_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 NULLab_shortdesc tinytext NOT NULLab_article longtext NOT NULLab_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?