Creating and dealing with the customer structure tables

We will be using MySQL, and the database character is set to utf8 and collation is set to utf8_bin. When SQL describes the details of what we defined previously, each of these components are as follows.

The User table

The User table we prepared earlier becomes operational when the following code is executed. It's important to remember to include AUTO_INCREMENT in the id column; otherwise, you have to input it manually:

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS 'users';
CREATE TABLE 'users' (
  'id' bigint(20) NOT NULL AUTO_INCREMENT,
  'status' tinyint(1) NOT NULL DEFAULT '1',
  'email' varchar(255) NOT NULL,
  'passwd' char(40) NOT NULL,
  'lastname' varchar(20) NOT NULL,
  'firstname' varchar(20) NOT NULL,
  'modified' datetime DEFAULT NULL,
  'created' datetime NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;

The Customer table

Once the following code is executed, the Customer table becomes operational:

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS 'customers';
CREATE TABLE 'customers' (
  'id' bigint(20) NOT NULL AUTO_INCREMENT,
  'status' tinyint(1) NOT NULL DEFAULT '1',
  'name' varchar(255) NOT NULL,
  'addr1' varchar(255) NOT NULL,
  'addr2' varchar(255) DEFAULT NULL,
  'city' varchar(50) NOT NULL,
  'state' varchar(50) NOT NULL,
  'zip' varchar(10) NOT NULL,
  'country' varchar(50) NOT NULL,
  'phone' varchar(50) NOT NULL,
  'fax' varchar(50) DEFAULT NULL,
  'modified' datetime DEFAULT NULL,
  'created' datetime NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;

This is the foundation of creating an initial set of tables that can later be populated with data.

The Quotation table

This is the corresponding code for the Quotation table. As with the Customer table, this code snippet will lay the foundation of our table.

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS 'quotation';
CREATE TABLE 'quotation' (
  'id' bigint(20) NOT NULL AUTO_INCREMENT,
  'status' tinyint(1) NOT NULL DEFAULT '1',
  'customer' bigint(20) NOT NULL,
  'note' text NOT NULL,
  'modified' datetime DEFAULT NULL,
  'created' datetime NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS 'quotations';
CREATE TABLE 'quotations' (
  'id' bigint(20) NOT NULL AUTO_INCREMENT,
  'status' tinyint(1) NOT NULL DEFAULT '1',
  'parent' bigint(20) NOT NULL,
  'description' varchar(255) NOT NULL,
  'qty' int(11) NOT NULL,
  'price' int(11) NOT NULL,
  'sum' int(11) NOT NULL,
  'modified' datetime DEFAULT NULL,
  'created' datetime NOT NULL,
  PRIMARY KEY ('id'),
  KEY 'parent' ('parent')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;

The Bill table

As with the previous two code snippets, the following code for the Bill table is very similar to the Quotation table, so this can be found in the source file under 04_bill_table.sql.

These are all the tables we need for this database. Now let's move on to testing after creating each operation.