Ext JS Data-driven Application Design

First, let's look at the structure of the application we're about to build. This is a sales management application built for the user to register customers, send quotations for orders, and finally to invoice the customer with a bill.

The user can input data in to the Customer table. The customer can be an individual or a company, either way, each customer receives a unique ID.

The Quotation table represents the final quotation sent to the customer. The Quotations table contains the individual items being ordered in the quotation.

A bill is the final invoice sent to the customer. As with the Quotations table, the Bills table refers to the individual items ordered by the customer.

The user data is a simple structure that is used to log in to a system. It has an e-mail address, a password, and a name.

Do not delete the user data and physically manage it with a flag. It is connected to other data structures with joint ownership, recording the date and time when it was created along with the updated date and time.

When we design a table with a model of MySQL, it looks similar to the following table. After having carried out MD5, we perform SHA1. Then, we will have 40 characters and can store the password.

The customer data contains the name and address of the company or client. It lets the Quotation and Bill tables perform a relation of this data and use the data. Being the master data, adding to and deleting from the user interface is not available at this time. However, as you develop the application, you eventually should be able to edit this data.

The following screenshot shows the input fields for registering a customer. The sections under the Name column are the fields that need to be filled in for each customer. The Type column refers to the type of data to be entered, such as words, numbers, and dates. The Key column allows data to be referenced between different tables.

The Quotation and Quotations tables have a 1-N relationship.

In Quotation, you can save the basic information of the document, and in Quotations you can store each item being ordered.

Quotation

This following screenshot shows the fields necessary for Quotation. The table headings are the same as in the Customer table explained previously, so let's fill this out accordingly.

Quotations

This is the same as before, so let's go ahead and fill this out. The parent refers to the overall quotation that the Quotations (individual items) table belongs to.

The Bill table is almost the same as the Quotation table. However, the Bill table can sometimes contain the ID of an associated Quotation table.

Bill

The following screenshot shows the Bill table:

Bills

Similar to Quotations, in Bills you can store each item that is ordered:

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.

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;

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;

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;

Because we will use PHP in later stages, let's prepare each operation now. Here, we will insert some temporary data.

Remember to check that the acquisition and update operations are working properly.

SELECT
    COUNT(id) as auth
FROM
    users
WHERE
    users.email = 'extkazuhiro@xenophy.com'
AND
    users.passwd = SHA1(MD5('password'))
AND
    users.status = 1;

SELECT
    users.id,
    users.email,
    users.lastname,
    users.firstname
FROM
    users
WHERE
    users.status = 1;

INSERT INTO users (
    email,
    passwd,
    lastname,
    firstname,
    modified,
    created
) VALUES (
    'someone@xenophy.com',
    SHA1(MD5('password')),
    'Kotsutsumi',
    'Kazuhiro',
    NOW(),
    NOW()
);

UPDATE
    users
SET
    email='extkazuhiro@xenophy.com',
    passwd=SHA1(MD5('password')),
    lastname='Kotsutsumi',
    firstname='Kazuhiro',
    modified=NOW()
WHERE
    id=1

UPDATE
    users
SET
    status=9
WHERE
    id=1

Although Add, Update, and Delete are necessary operations, we'll come to these in the later chapter, so we can leave it out at this time.

SELECT
    customers.id,
    customers.name,
    customers.addr1,
    customers.addr2,
    customers.city,
    customers.state,
    customers.zip,
    customers.country,
    customers.phone,
    customers.fax
FROM
    customers
WHERE
    customers.status = 1;

SELECT quotations.description, 
  quotations.qty, 
  quotations.price, 
  quotations.sum
FROM
  quotations
WHERE
  quotations.'status' = 1
AND
  quotations.parent = 1

Again let's leave out Add, Update and Delete for now because the Bill table is similar to what preceded this.

It's straightforward to say that once a quotation has been accepted, a bill is produced. Therefore, in data structures such as ours, Quotation and Bill are related. The only difference is that Bill contains the extra Quotation ID to create the relationship between the two.

Also, remember the customer information list is almost the same as the quotation list.

In this chapter, we have defined the structure of the database we will use in this book.

You might have your own databases that you want to present in Ext JS. This is just a sample database that we can build on in the coming chapters.

In the next chapter we will begin the process of building the whole application. Don't worry, we'll explain each step.