Database schema - PHP

Because we are starting the series off with invoice management, we are going to be building the invoices table, so

CREATE TABLE `invoices` (
  `invno` int(4) unsigned zerofill NOT NULL auto_increment,
   `status` enum('Paid','Unpaid') NOT NULL default 'Unpaid',
  `VAT` decimal(9,2) NOT NULL default '0.00',
  `inv_date` date default NULL,
    `cid` int(11) NOT NULL default '0',
  `uID` int(4) NOT NULL default '0',
  PRIMARY KEY  (`invno`)
)

Let's run through the table fields:

The invno field is the primary key generator which gives a unique number to every new invoice that is created. The invoice numbers will be in the format 0001, 0002, 0003, and so on. The status field is of enumerate type and contains the values Paid and Unpaid. These values will be used to show the status of an invoice.

The Inv_date  field will show the date that the invoice was issued. VAT, by the way, will be calculated automatically. The cid is the foreign key that will help identify the client name associated with an invoice. The uID field identifies the user who created the invoice. This table is at the heart of the program; it will tell us everything we want to know about a particular invoice, such as how many invoices a particular user issued or how many invoices were made to a particular client etc.

The next table we are going to create is called "clientinv." It is going to hold specific invoice and client details, such as the description of the invoice and the total cost of a invoice:

CREATE TABLE `clientinv` (
  `invnum` int(4) unsigned zerofill NOT NULL auto_increment,
  `descr` text NOT NULL,
  `totxVAT` decimal(9,2) NOT NULL default '0.00',
  `totwVAT` decimal(9,2) NOT NULL default '0.00',
  `finv` int(11) NOT NULL default '0',
  PRIMARY KEY  (`invnum`)
)

Let's go through the table fields:

The "descr" field will hold the description of the invoice. The totxVAT and totwVAT fields will store the total cost of the product with and without VAT. The "finv" field is the foreign key that will hold the invoice number. I created this table because it will give us flexibility in terms of how we store information. Remember, one client can have many invoices and one invoice can contain information on many different products, which I could not store in either the invoices or the (soon to be created)client table.