[UPHPU] Invoices & Billing

[Scott Hill]

On 11/16/06, Daniel C. <dcrookston at gmail.com> wrote:
>
> The problem: come up with a way to store information about invoices
> that is tidy (in all senses of the word) and does not change the old
> invoices as rates change in the future.


You mentioned  tying rates to invoices.  I once did a time and billing
package for a client where we had to have the ability to use different rates
depending on the task or operation being performed and the employee doing
the work.  They wanted many different rates for all employees and to be able
to use different rates in the future.  I have attached a MySQL schema dump
of the database I used (I think this is the one) so you can check it out.  I
thought I had a Visio diagram but I can't seem to find it now.  The
"oprates" table is what ties the operations and employees together.  This
might give you some ideas on how to solve your problem.

-- 
Scott Hill

"May you solve interesting problems" - Author Unknown
"A fanatic is one who can't change his mind and won't change the subject." -
Sir Winston Churchill
-------------- next part --------------
# MySQL-Front 3.2  (Build 11.2)

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;

/*!40101 SET NAMES latin1 */;
DROP TABLE IF EXISTS `addresses`;
CREATE TABLE `addresses` (
  `adr_id` int(11) NOT NULL auto_increment,
  `adr_desc` varchar(40) default NULL,
  `adr_addr1` varchar(40) default NULL,
  `adr_addr2` varchar(40) default NULL,
  `adr_city` varchar(40) default NULL,
  `adr_state` char(2) default NULL,
  `adr_zip` varchar(10) default NULL,
  PRIMARY KEY  (`adr_id`),
  KEY `idx_adrdesc` (`adr_desc`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `clients`;
CREATE TABLE `clients` (
  `cln_id` int(11) NOT NULL auto_increment,
  `cln_name` varchar(40) default NULL,
  `cln_type` varchar(10) default NULL,
  `cln_status` varchar(10) default NULL,
  `cln_total` double(10,2) default NULL,
  `cln_clcid` int(11) default NULL,
  PRIMARY KEY  (`cln_id`),
  KEY `idx_clnname` (`cln_name`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `clnaddrs`;
CREATE TABLE `clnaddrs` (
  `cad_id` int(11) NOT NULL auto_increment,
  `cad_clnid` int(11) default NULL,
  `cad_adrid` int(11) default NULL,
  PRIMARY KEY  (`cad_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `clncats`;
CREATE TABLE `clncats` (
  `clc_id` int(11) NOT NULL auto_increment,
  `clc_name` varchar(40) default NULL,
  PRIMARY KEY  (`clc_id`),
  KEY `idx_clcname` (`clc_name`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `clnnotes`;
CREATE TABLE `clnnotes` (
  `cnt_id` int(11) NOT NULL auto_increment,
  `cnt_notid` int(11) default NULL,
  `cnt_clnid` int(11) default NULL,
  PRIMARY KEY  (`cnt_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `clnphns`;
CREATE TABLE `clnphns` (
  `cph_id` int(11) NOT NULL auto_increment,
  `cph_phnid` int(11) default NULL,
  `cph_clnid` int(11) default NULL,
  PRIMARY KEY  (`cph_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `empaddrs`;
CREATE TABLE `empaddrs` (
  `ead_id` int(11) NOT NULL auto_increment,
  `ead_adrid` int(11) default NULL,
  `ead_empid` int(11) default NULL,
  PRIMARY KEY  (`ead_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
  `emp_id` int(11) NOT NULL auto_increment,
  `emp_num` varchar(20) default NULL,
  `emp_lname` varchar(20) default NULL,
  `emp_fname` varchar(20) default NULL,
  `emp_ssn` varchar(11) default NULL,
  `emp_type` varchar(10) default NULL,
  `emp_status` varchar(10) default NULL,
  `emp_hiredate` date default NULL,
  `emp_birthdate` date default NULL,
  `emp_termdate` date default NULL,
  `emp_total` double(10,2) default NULL,
  `emp_pswd` varchar(20) default NULL,
  PRIMARY KEY  (`emp_id`),
  KEY `idx_empnum` (`emp_num`),
  KEY `idx_emplastfirst` (`emp_lname`,`emp_fname`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `empnotes`;
CREATE TABLE `empnotes` (
  `ent_id` int(11) NOT NULL auto_increment,
  `ent_notid` int(11) default NULL,
  `ent_empid` int(11) default NULL,
  PRIMARY KEY  (`ent_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `empphns`;
CREATE TABLE `empphns` (
  `eph_id` int(11) NOT NULL auto_increment,
  `eph_phnid` int(11) default NULL,
  `eph_empid` int(11) default NULL,
  PRIMARY KEY  (`eph_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `estimates`;
CREATE TABLE `estimates` (
  `est_id` int(11) NOT NULL auto_increment,
  `est_name` varchar(40) default NULL,
  `est_number` varchar(20) default NULL,
  `est_total` double(10,2) default NULL,
  `est_prjid` int(11) default NULL,
  PRIMARY KEY  (`est_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `expcodes`;
CREATE TABLE `expcodes` (
  `ecd_id` int(11) NOT NULL auto_increment,
  `ecd_num` varchar(20) default NULL,
  `ecd_name` varchar(40) default NULL,
  PRIMARY KEY  (`ecd_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `notes`;
CREATE TABLE `notes` (
  `not_id` int(11) NOT NULL auto_increment,
  `not_desc` varchar(40) default NULL,
  `not_text` mediumtext,
  PRIMARY KEY  (`not_id`),
  KEY `idx_notdesc` (`not_desc`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `operations`;
CREATE TABLE `operations` (
  `opr_id` int(11) NOT NULL auto_increment,
  `opr_number` varchar(20) default NULL,
  `opr_name` varchar(40) default NULL,
  PRIMARY KEY  (`opr_id`),
  KEY `idx_oprname` (`opr_name`),
  KEY `idx_oprnum` (`opr_number`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `oprates`;
CREATE TABLE `oprates` (
  `opt_id` int(11) NOT NULL auto_increment,
  `opt_oprid` int(11) default NULL,
  `opt_empid` int(11) default NULL,
  `opt_rate` double default NULL,
  PRIMARY KEY  (`opt_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `oprnotes`;
CREATE TABLE `oprnotes` (
  `opn_id` int(11) NOT NULL auto_increment,
  `opn_oprid` int(11) default NULL,
  `opn_notid` int(11) default NULL,
  PRIMARY KEY  (`opn_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `pmt_id` int(11) NOT NULL auto_increment,
  `pmt_date` date default NULL,
  `pmt_amount` double default NULL,
  `pmt_ptdid` int(11) default NULL,
  `pmt_clnid` int(11) default NULL,
  PRIMARY KEY  (`pmt_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `paymeths`;
CREATE TABLE `paymeths` (
  `ptd_id` int(11) NOT NULL auto_increment,
  `ptd_num` varchar(20) default NULL,
  `ptd_name` varchar(40) default NULL,
  PRIMARY KEY  (`ptd_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `phases`;
CREATE TABLE `phases` (
  `phs_id` int(11) NOT NULL auto_increment,
  `phs_name` varchar(40) default NULL,
  `phs_startdate` date default NULL,
  `phs_promdate` date default NULL,
  `phs_compdate` date default NULL,
  `phs_enddate` date default NULL,
  `phs_prjid` int(11) default NULL,
  PRIMARY KEY  (`phs_id`),
  KEY `idx_phsname` (`phs_name`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `phones`;
CREATE TABLE `phones` (
  `phn_id` int(11) NOT NULL auto_increment,
  `phn_desc` varchar(40) default NULL,
  `phn_num` varchar(14) default NULL,
  `phn_ac` smallint(6) default NULL,
  `phn_ext` varchar(6) default NULL,
  PRIMARY KEY  (`phn_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `phsnotes`;
CREATE TABLE `phsnotes` (
  `phs_id` int(11) NOT NULL auto_increment,
  `phs_phsid` int(11) default NULL,
  `phs_notid` int(11) default NULL,
  PRIMARY KEY  (`phs_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `prjnotes`;
CREATE TABLE `prjnotes` (
  `pjn_id` int(11) NOT NULL auto_increment,
  `pjn_prjid` int(11) default NULL,
  `pjn_notid` int(11) default NULL,
  PRIMARY KEY  (`pjn_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `projects`;
CREATE TABLE `projects` (
  `prj_id` int(11) NOT NULL auto_increment,
  `prj_desc` varchar(40) default NULL,
  `prj_num` varchar(20) default NULL,
  `prj_total` double(10,2) default NULL,
  `prj_phases` smallint(6) default NULL,
  `prj_status` varchar(10) default NULL,
  `prj_bidamt` double(10,2) default NULL,
  `prj_startdate` date default NULL,
  `prj_promdate` date default NULL,
  `prj_compdate` date default NULL,
  `prj_enddate` date default NULL,
  `prj_clnid` int(11) default NULL,
  PRIMARY KEY  (`prj_id`),
  KEY `idx_prjdesc` (`prj_desc`),
  KEY `idx_prjnum` (`prj_num`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `tcexp`;
CREATE TABLE `tcexp` (
  `tce_id` int(11) NOT NULL auto_increment,
  `tce_date` date default NULL,
  `tce_amount` double default NULL,
  `tce_ecdid` int(11) default NULL,
  PRIMARY KEY  (`tce_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `tcnotes`;
CREATE TABLE `tcnotes` (
  `tcn_id` int(11) NOT NULL auto_increment,
  `tcn_tcdid` int(11) default NULL,
  `tcn_notid` int(11) default NULL,
  PRIMARY KEY  (`tcn_id`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS `timecards`;
CREATE TABLE `timecards` (
  `tcd_id` int(11) NOT NULL auto_increment,
  `tcd_date` date default NULL,
  `tcd_beg` varchar(8) default NULL,
  `tcd_end` varchar(8) default NULL,
  `tcd_elapsed` double default NULL,
  `tcd_prjid` int(11) default NULL,
  `tcd_phsid` int(11) default NULL,
  `tcd_empid` int(11) default NULL,
  `tcd_oprid` int(11) default NULL,
  PRIMARY KEY  (`tcd_id`)
) TYPE=MyISAM;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;