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 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;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.
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;