Designing new CMS

Hi friends,

First I would like to thank you all SP members who helped me a lot in developing my first CMS. This time i am re-developing that CMS with some new features & clean code. For that I have re-designed database as below


CREATE TABLE IF NOT EXISTS `insurers` (
  `sr_no` int(5) NOT NULL AUTO_INCREMENT,
  `insurers` varchar(255) NOT NULL,
  PRIMARY KEY (`sr_no`),
  UNIQUE KEY `insurers` (`insurers`)
) ENGINE=MyISAM ;


CREATE TABLE IF NOT EXISTS `issuing_office` (
  `sr_no` int(5) NOT NULL AUTO_INCREMENT,
  `issuing_office` varchar(255) NOT NULL,
  PRIMARY KEY (`sr_no`),
  UNIQUE KEY `issuing_office` (`issuing_office`)
) ENGINE=MyISAM ;


CREATE TABLE IF NOT EXISTS `ledger` (
  `sr_no` int(5) NOT NULL AUTO_INCREMENT,
  `bill_no` int(4) unsigned zerofill NOT NULL,
  `bill_date` date NOT NULL,
  `ref_no` varchar(25) NOT NULL,
  `insured_name` varchar(255) NOT NULL,
  `vehicle` varchar(35) NOT NULL,
  `vehicle_no` varchar(35) NOT NULL,
  `date_of_loss` date NOT NULL,
  `date_of_survey` date NOT NULL,
  `type_of_report` varchar(35) NOT NULL,
  `claim_no` varchar(35) NOT NULL,
  `policy_no` varchar(35) NOT NULL,
  `insurers` varchar(255) NOT NULL,
  `issuing_office` varchar(200) NOT NULL,
  `survey_fees` decimal(9,2) NOT NULL DEFAULT '0.00',
  `received_fees` decimal(9,2) NOT NULL DEFAULT '0.00',
  `cheque_no` int(6) unsigned zerofill NOT NULL DEFAULT '000000',
  `cheque_date` date NOT NULL DEFAULT '0000-00-00',
  `cheque_amt` decimal(9,2) NOT NULL DEFAULT '0.00',
  `received_on` date NOT NULL DEFAULT '0000-00-00',
  `remarks` text NOT NULL,
  `fin_year` text NOT NULL,
  `record_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`sr_no`),
  UNIQUE KEY `bill_no` (`bill_no`)
) ENGINE=MyISAM ;


CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(2) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(16) NOT NULL,
  `user_password` varchar(50) NOT NULL,
  `user_rank` varchar(30) NOT NULL,
  `last_login` datetime NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM ;


CREATE TABLE IF NOT EXISTS `reminders` (
  `sr_no` int(5) NOT NULL AUTO_INCREMENT,
  `reminder` text NOT NULL,
  `reminder_added_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `reminder_schedule` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`sr_no`)
) ENGINE=MyISAM ;

  1. First ‘Insurers’ table will be used only to generate a dropdown menu. Then selected field data will be inserted into ‘Ledger’ table’s ‘insurers’ field.

  2. Second ‘issuing_office’ table will be used only to generate a dropdown menu. Then selected field data will be inserted into ‘Ledger’ table’s ‘issuing_office’ field.

  3. Third table ‘Ledger’ contains 24 fields. At the initial stage data will be inserted in only 19 fields.

  `sr_no`
  `bill_no`
  `bill_date`
  `ref_no` 
  `insured_name`
  `vehicle` 
  `vehicle_no` 
  `date_of_loss`
  `date_of_survey`
  `type_of_report`
  `claim_no`
  `policy_no`
  `insurers`
  `issuing_office`
  `survey_fees`
  `remarks`
  `fin_year`   //will store year like 2009-2010 ,  2010-2011 ,   2011-2012 etc
  `record_time`   // will store current datetime
  `user_name`    // logged_in user name

Later on when the fee will be paid by the company then these fields will be updated. (Expecting less than 9999 record in ledger table)


  `received_fees`
  `cheque_no`
  `cheque_date`
  `cheque_amt`
  `received_on`
  1. User table to store user details. (Expecting around max 100 users)
  2. Reminder table to add important details that will be displayed as html marquee at the bottom of the page.

This is database structure i have designed. I also need PM (personal message system). So I need suggestion on this. Should I made any changes in database structure to improve performance.

On your ledger table I see you have a field issuing_office and you set it to varchar(255). I’m assuming that the issuing_office field is a foreign key to the ledger table from the issuing_office table? If thats the case I would use the primary key of the issuing_office table for the foreign key in the ledger table… Id do this as well for the relation to the insures table.

Instead of issuing_office in the ledger table I’d go issuing_office_no

issuing_office.sr_no = ledger.issuing_office_no

I don’t see any indexes. Those improve performance.