Database tables and Model classes are where most CakePHP applications begin. With this example, we will focus on Model associations rather than providing a complete tutorial of an application. Consider this a solid foundation for building an Accounting Web App, this is the M in MVC.

The idea behind what we are modeling today is a simple accounting web app similar to Freshbooks or something QuickBooks offers. The model definitions and relationships are as follows.

Company – Represents the provider company within the accounting system. A Company has many Groups, Users, and Clients. Additionally, a Company has many Items, Estimates, Invoices, and Payments.

User – Represents an employee of the Company. A User belongs to a Company and many Groups through GroupMembership. As well, User are who will login and use the application.

Client – Represents the paying organization that is your customer. A Client has many contacts, estimates, invoices, and payments.

Contact – Represents an employee of a Client Company. A Contact belongs to a Client.

Estimate – Has Many EstimateItems and belongs to both a Company(Issuer) and a Client(Recipent).

Invoice – Has Many InvoiceItems and Payments. Belongs to both a Company(Issuer) and a Client(Recipent).

Payment – Belongs to both an Invoice, Client(Payor) and a Company(Payee).

Client.php

<?php
App::uses('AppModel', 'Model');

class Client extends AppModel {
    public $displayField = 'name';
    
    public $hasMany = array('Invoice', 'Payment', 'Estimate' ,'Contact' );
    
    public $belongsTo = array( 'Company' );

}

Company.php

<?php
App::uses('AppModel', 'Model');

class Company extends AppModel {
    
    public $hasMany = array('Client','User','Group' );

}

Contact.php

<?php
App::uses('AppModel', 'Model');

class Contact extends AppModel {
    public $displayField = 'last_name';
    
    public $belongsTo = array( 'Client' );
    

}

Estimate.php

<?php
App::uses('AppModel', 'Model');

class Estimate extends AppModel {
    public $displayField = 'number';
    
    public $hasMany = array('EstimateItem' );
    
    public $belongsTo = array( 'Company', 'Client', 'TaxRate' );

}

EstimateItem.php

<?php
App::uses('AppModel', 'Model');

class EstimateItem extends AppModel {
    public $displayField = 'item';

    public $belongsTo = array( 'Estimate' );

}

Group.php

<?php
App::uses('AppModel', 'Model');

class Group extends AppModel {
    
    public $hasMany = array(
        'Children'=>array(
            'className'=>'Group',
            'foreignKey'=>'parent_id',
            'order' => 'parent_id ASC'
        ),
        'GroupMembership'
    );
    public $belongsTo = array(
        'Company', 
        'Parent'=>array(
            'className'=>'Group',
            'foreignKey'=>'parent_id'
        )                             
         
    );

}

GroupMembership.php

<?php
App::uses( 'AppModel', 'Model' );

class GroupMembership extends AppModel {
    public $belongsTo = array(
        'User', 'Group'
    );   
}

Industry.php

<?php
App::uses('AppModel', 'Model');

class Industry extends AppModel {
    public $displayField = 'name';
}

Invoice.php

<?php
App::uses('AppModel', 'Model');

class Invoice extends AppModel {
    public $displayField = 'number';
    
    public $hasMany = array('InvoiceItem', 'Payment' );
    
    public $belongsTo = array( 'Company', 'Client', 'TaxRate' );

}

InvoiceItem.php

<?php
App::uses('AppModel', 'Model');

class InvoiceItem extends AppModel {
    public $displayField = 'item';

    public $belongsTo = array( 'Invoice' );

}

Item.php

<?php
App::uses('AppModel', 'Model');

class Item extends AppModel {
    public $displayField = 'item';

    public $belongsTo = array('company');
}

Payment.php

<?php
App::uses('AppModel', 'Model');

class Payment extends AppModel {
    public $displayField = 'number';
    
    public $belongsTo = array( 'Company','Invoice', 'Client', 'PaymentMethod' );

}

TaxRate.php

<?php
App::uses('AppModel', 'Model');

class TaxRate extends AppModel {
    var $belongsTo = array('Company');

}

User.php

<?php
App::uses('AppModel', 'Model');

class User extends AppModel {
    public $displayField = 'email';

    public $belongsTo = array('Company');
    public $hasMany = array('GroupMembership');
    
}

Database Schema

Below is the schema file generated using the CakePHP console command ‘cake schema generate’.

<?php
class AppSchema extends CakeSchema {

	public $file = 'cakebilling.php';

	public function before($event = array()) {
		return true;
	}

	public function after($event = array()) {
	}

	public $clients = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'number' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 23, 'key' => 'unique', 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'name' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'country' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 3, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'street_1' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'street_2' => array('type' => 'string', 'null' => true, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'city' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'state' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'postal_code' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 10, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'industry' => array('type' => 'string', 'null' => true, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'company_size' => array('type' => 'string', 'null' => true, 'default' => NULL, 'length' => 12, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'business_phone' => array('type' => 'string', 'null' => true, 'default' => NULL, 'length' => 20, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'fax' => array('type' => 'string', 'null' => true, 'default' => NULL, 'length' => 20, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'invoice_preference' => array('type' => 'string', 'null' => false, 'default' => 'email', 'length' => 10, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'internal_notes' => array('type' => 'text', 'null' => true, 'default' => NULL, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'company_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'created' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'updated' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'number' => array('column' => 'number', 'unique' => 1), 'company_id' => array('column' => 'company_id', 'unique' => 0)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
	public $companies = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'name' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'profession' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'currency' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 3, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'country' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 3, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'street_1' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'street_2' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'city' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'state' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'postal_code' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 10, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'email' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 128, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'business_phone' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 24, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'fax' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 24, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'mobile' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 24, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'account_url' => array('type' => 'string', 'null' => false, 'default' => NULL, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'next_estimate' => array('type' => 'string', 'null' => true, 'default' => NULL, 'length' => 23, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'next_invoice' => array('type' => 'string', 'null' => true, 'default' => NULL, 'length' => 23, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'next_payment' => array('type' => 'string', 'null' => true, 'default' => NULL, 'length' => 23, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'timezone_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 5),
		'created' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'updated' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
	public $contacts = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'first_name' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'last_name' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'email' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 128, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'phone' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 24, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'mobile' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 24, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'client_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'created' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'updated' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'client_id' => array('column' => 'client_id', 'unique' => 0)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
	public $estimate_items = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'quantity' => array('type' => 'integer', 'null' => false, 'default' => NULL),
		'item' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'description' => array('type' => 'text', 'null' => false, 'default' => NULL, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'unit_cost' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'line_total' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'order' => array('type' => 'integer', 'null' => false, 'default' => '10', 'length' => 10),
		'estimate_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'invoice_id' => array('column' => 'estimate_id', 'unique' => 0)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
	public $estimates = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'number' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 16, 'key' => 'unique', 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'notes' => array('type' => 'text', 'null' => false, 'default' => NULL, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'terms' => array('type' => 'text', 'null' => false, 'default' => NULL, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'subtotal' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'total' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'amount_paid' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'tax_rate' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '5,3'),
		'tax_rate_id' => array('type' => 'integer', 'null' => true, 'default' => NULL, 'length' => 10),
		'client_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'company_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'created' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'updated' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'number' => array('column' => 'number', 'unique' => 1), 'client_id' => array('column' => 'client_id', 'unique' => 0), 'company_id' => array('column' => 'company_id', 'unique' => 0)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
	public $group_memberships = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'group_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10),
		'user_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
	public $groups = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'name' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 32, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'parent_id' => array('type' => 'integer', 'null' => true, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'company_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'key' => 'index'),
		'created' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'company_id' => array('column' => 'company_id', 'unique' => 0), 'parent_id' => array('column' => 'parent_id', 'unique' => 0)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
	public $industries = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'name' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
	public $invoice_items = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'quantity' => array('type' => 'integer', 'null' => false, 'default' => NULL),
		'item' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'description' => array('type' => 'text', 'null' => false, 'default' => NULL, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'unit_cost' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'line_total' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'order' => array('type' => 'integer', 'null' => false, 'default' => '10', 'length' => 10),
		'invoice_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'invoice_id' => array('column' => 'invoice_id', 'unique' => 0)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
	public $invoices = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'number' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 16, 'key' => 'unique', 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'notes' => array('type' => 'text', 'null' => false, 'default' => NULL, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'terms' => array('type' => 'text', 'null' => false, 'default' => NULL, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'subtotal' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'total' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'amount_paid' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'tax_rate' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '5,3'),
		'tax_rate_id' => array('type' => 'integer', 'null' => true, 'default' => NULL, 'length' => 10),
		'client_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'company_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'created' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'updated' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'number' => array('column' => 'number', 'unique' => 1), 'client_id' => array('column' => 'client_id', 'unique' => 0), 'company_id' => array('column' => 'company_id', 'unique' => 0)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
	public $items = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'item' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'key' => 'unique', 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'description' => array('type' => 'text', 'null' => false, 'default' => NULL, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'cost' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'price' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'msrp' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'stock' => array('type' => 'integer', 'null' => true, 'default' => NULL, 'length' => 10),
		'company_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'created' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'updated' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'item' => array('column' => 'item', 'unique' => 1), 'company_id' => array('column' => 'company_id', 'unique' => 0)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
	public $payments = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'number' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 23, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'client_name' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'date' => array('type' => 'date', 'null' => true, 'default' => NULL),
		'currency' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 3, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'amount' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '8,2'),
		'reference' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 23, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'notes' => array('type' => 'text', 'null' => false, 'default' => NULL, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'client_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'payment_method_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10),
		'invoice_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'company_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'created' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'updated' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'invoice_id' => array('column' => 'invoice_id', 'unique' => 0), 'client_id' => array('column' => 'client_id', 'unique' => 0), 'company_id' => array('column' => 'company_id', 'unique' => 0)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
	public $tax_rates = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'name' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 32, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'rate' => array('type' => 'float', 'null' => false, 'default' => NULL, 'length' => '5,3'),
		'number' => array('type' => 'integer', 'null' => false, 'default' => NULL),
		'company_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'created' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'updated' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'company_id' => array('column' => 'company_id', 'unique' => 0)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
	public $users = array(
		'id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'primary'),
		'email' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 128, 'key' => 'unique', 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'username' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 128, 'key' => 'unique', 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'password' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 40, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'first_name' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'last_name' => array('type' => 'string', 'null' => false, 'default' => NULL, 'length' => 64, 'collate' => 'utf8_general_ci', 'charset' => 'utf8'),
		'company_id' => array('type' => 'integer', 'null' => false, 'default' => NULL, 'length' => 10, 'key' => 'index'),
		'created' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'updated' => array('type' => 'datetime', 'null' => true, 'default' => NULL),
		'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'email' => array('column' => 'email', 'unique' => 1), 'username' => array('column' => 'username', 'unique' => 1), 'company_id' => array('column' => 'company_id', 'unique' => 0)),
		'tableParameters' => array('charset' => 'utf8', 'collate' => 'utf8_general_ci', 'engine' => 'MyISAM')
	);
}

MySQL Data Dump

Note: In the SQL below the table names have a prefix of pro_, you will need to either change your app/Config/database.php to use this extension or open the sql in your favorite text editor and search and replace ‘pro_’.
--
-- Table structure for table `pro_audit_logs`
--

CREATE TABLE IF NOT EXISTS `pro_audit_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `action` varchar(12) NOT NULL,
  `detail` text NOT NULL,
  `model` varchar(23) NOT NULL,
  `foreign_id` int(10) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fkey` (`model`,`foreign_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_clients`
--

CREATE TABLE IF NOT EXISTS `pro_clients` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `number` varchar(23) NOT NULL,
  `name` varchar(64) NOT NULL,
  `country` char(3) NOT NULL,
  `street_1` varchar(64) NOT NULL,
  `street_2` varchar(64) DEFAULT NULL,
  `city` varchar(64) NOT NULL,
  `state` varchar(64) NOT NULL,
  `postal_code` varchar(10) NOT NULL,
  `industry` varchar(64) DEFAULT NULL,
  `company_size` varchar(12) DEFAULT NULL,
  `business_phone` varchar(20) DEFAULT NULL,
  `fax` varchar(20) DEFAULT NULL,
  `invoice_preference` varchar(10) NOT NULL DEFAULT 'email',
  `internal_notes` text,
  `company_id` int(10) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `number` (`number`),
  KEY `company_id` (`company_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_companies`
--

CREATE TABLE IF NOT EXISTS `pro_companies` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `profession` varchar(64) NOT NULL,
  `currency` char(3) NOT NULL,
  `country` char(3) NOT NULL,
  `street_1` varchar(64) NOT NULL,
  `street_2` varchar(64) NOT NULL,
  `city` varchar(64) NOT NULL,
  `state` varchar(64) NOT NULL,
  `postal_code` varchar(10) NOT NULL,
  `email` varchar(128) NOT NULL,
  `business_phone` varchar(24) NOT NULL,
  `fax` varchar(24) NOT NULL,
  `mobile` varchar(24) NOT NULL,
  `account_url` varchar(255) NOT NULL,
  `next_estimate` varchar(23) DEFAULT NULL,
  `next_invoice` varchar(23) DEFAULT NULL,
  `next_payment` varchar(23) DEFAULT NULL,
  `timezone_id` smallint(5) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_contacts`
--

CREATE TABLE IF NOT EXISTS `pro_contacts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(64) NOT NULL,
  `last_name` varchar(64) NOT NULL,
  `email` varchar(128) NOT NULL,
  `phone` varchar(24) NOT NULL,
  `mobile` varchar(24) NOT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_estimates`
--

CREATE TABLE IF NOT EXISTS `pro_estimates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `number` varchar(16) NOT NULL,
  `notes` text NOT NULL,
  `terms` text NOT NULL,
  `subtotal` decimal(8,2) NOT NULL,
  `total` decimal(8,2) NOT NULL,
  `amount_paid` decimal(8,2) NOT NULL,
  `tax_rate` decimal(5,3) NOT NULL,
  `tax_rate_id` int(10) unsigned DEFAULT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `company_id` int(10) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `number` (`number`),
  KEY `client_id` (`client_id`),
  KEY `company_id` (`company_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_estimate_items`
--

CREATE TABLE IF NOT EXISTS `pro_estimate_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `quantity` int(11) NOT NULL,
  `item` varchar(64) NOT NULL,
  `description` text NOT NULL,
  `unit_cost` decimal(8,2) NOT NULL,
  `line_total` decimal(8,2) NOT NULL,
  `order` int(10) unsigned NOT NULL DEFAULT '10',
  `estimate_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`estimate_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_groups`
--

CREATE TABLE IF NOT EXISTS `pro_groups` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `company_id` int(11) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `parent_id` (`parent_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_group_memberships`
--

CREATE TABLE IF NOT EXISTS `pro_group_memberships` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_industries`
--

CREATE TABLE IF NOT EXISTS `pro_industries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=48 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_invoices`
--

CREATE TABLE IF NOT EXISTS `pro_invoices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `number` varchar(16) NOT NULL,
  `notes` text NOT NULL,
  `terms` text NOT NULL,
  `subtotal` decimal(8,2) NOT NULL,
  `total` decimal(8,2) NOT NULL,
  `amount_paid` decimal(8,2) NOT NULL,
  `tax_rate` decimal(5,3) NOT NULL,
  `tax_rate_id` int(10) unsigned DEFAULT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `company_id` int(10) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `number` (`number`),
  KEY `client_id` (`client_id`),
  KEY `company_id` (`company_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_invoice_items`
--

CREATE TABLE IF NOT EXISTS `pro_invoice_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `quantity` int(11) NOT NULL,
  `item` varchar(64) NOT NULL,
  `description` text NOT NULL,
  `unit_cost` decimal(8,2) NOT NULL,
  `line_total` decimal(8,2) NOT NULL,
  `order` int(10) unsigned NOT NULL DEFAULT '10',
  `invoice_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_items`
--

CREATE TABLE IF NOT EXISTS `pro_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item` varchar(64) NOT NULL,
  `description` text NOT NULL,
  `cost` decimal(8,2) NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `msrp` decimal(8,2) NOT NULL,
  `stock` int(10) unsigned DEFAULT NULL,
  `company_id` int(10) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `item` (`item`),
  KEY `company_id` (`company_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_payments`
--

CREATE TABLE IF NOT EXISTS `pro_payments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `number` varchar(23) NOT NULL,
  `client_name` varchar(64) NOT NULL,
  `date` date DEFAULT NULL,
  `currency` char(3) NOT NULL,
  `amount` decimal(8,2) NOT NULL,
  `reference` varchar(23) NOT NULL,
  `notes` text NOT NULL,
  `client_id` int(10) unsigned NOT NULL,
  `payment_method_id` int(10) unsigned NOT NULL,
  `invoice_id` int(10) unsigned NOT NULL,
  `company_id` int(10) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `client_id` (`client_id`),
  KEY `company_id` (`company_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_payment_methods`
--

CREATE TABLE IF NOT EXISTS `pro_payment_methods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(23) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_tax_rates`
--

CREATE TABLE IF NOT EXISTS `pro_tax_rates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `rate` decimal(5,3) NOT NULL,
  `number` int(11) NOT NULL,
  `company_id` int(10) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_timezones`
--

CREATE TABLE IF NOT EXISTS `pro_timezones` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `offset` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=83 ;

-- --------------------------------------------------------

--
-- Table structure for table `pro_users`
--

CREATE TABLE IF NOT EXISTS `pro_users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(128) NOT NULL,
  `username` varchar(128) NOT NULL,
  `password` varchar(40) NOT NULL,
  `first_name` varchar(64) NOT NULL,
  `last_name` varchar(64) NOT NULL,
  `company_id` int(10) unsigned NOT NULL,
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `username` (`username`),
  KEY `company_id` (`company_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

Keywords:

  • cakephp accounting system
  • cakephp invoice
  • invoice program in cakephp