The project that is built in this and the next chapter is based, in part, on the Zend Framework product. We use the Model-View-Controller (MVC) concepts that are part of this framework tool. To use it, you must have it installed, so refer to the preceding chapter for an introduction to the Zend Framework and the next chapter for installation and setup instructions as they pertain to this project.
We have chosen a mid-sized project that is designed to use all the major parts of Zend Studio for Eclipse and give you a small working application at the end of the process. The project is a Customer Relationship Management (CRM) application that tracks some basic data. It is not intended to be a fully operational production system. If you see this application as useful, by all means feel free to add data elements, tables, and further functionality to improve it.
The system for this example is menu based and user credential protected. The home page has a username (email) and password form to be passed before entry to the system will be granted.
After accessing the system, you have a menu system with all the major topics at the top level, with their respective functions (Add, Edit, Delete) available in submenu format.
The following major table elements are included:
Event Details Table (lookup table)
Country Table (lookup table)
System Users Information Table (system access credentials)
The People Information Table (see Table 17.1) tracks all the pertinent information on people for which you have contact. You can record a lot of their personal information such as phone number, cell number, email address, and so on. The system allows you to add, edit, and delete this table information.
Table 17.1. People Information Table Elements
Column Name | Description |
---|---|
| Primary key identifier for this table |
| First name of the person |
| Last name of the person |
| Company identifier (if applicable); the company that this person works for |
| The person’s main contact number |
| The person’s mobile phone (cellular) number |
| The person’s email address |
The Company Information Table (see Table 17.2) provides information that you can track on each company that you have dealings with. The system allows you to add, edit, and delete this table information. The people whose names are recorded in this application can be connected to the companies in this table so that you have to record their company information only once.
Table 17.2. Company Information Table Elements
Column Name | Description |
---|---|
| Primary key identifier for this table |
| The name of the company |
| First address line for the company |
| Second address line for the company |
| City where the company is located |
| Province or state where the company is located; can be drawn from a lookup table (not provided) |
| Postal or ZIP code of the company |
| The country ID code (from the lookup table) where the company is located |
The Event Information Table (see Table 17.3) records the activities or events that are performed toward the people in your system, such as phone conversations, mailings, faxes sent, and so on.
Table 17.3. Event Information Table Elements
Description | |
---|---|
| Primary key identifier for this table |
| Connection ID to the lookup table of event types |
| Date the event took place |
| Time the event took place |
| Connection ID of the person that this event relates to |
| All relevant notes or comments that you want to record of the event |
| Date when this event should be followed up |
The Event Details Table (see Table 17.4) is a lookup table of common events that you can draw from for the event information. Common events such as phone calls, birthdays, mailings, and so on are listed here for selection.
The Country Table (see Table 17.5) is a lookup table of commonly used country names for use when adding a company’s information.
The System Users Information Table (see Table 17.6) is the user access table where all the emails and passwords are kept to verify the credentials of those wishing to gain access to this system.
The SQL that is needed to create these tables as described in the preceding sections is shown in Listing 17.1. It is entered here for your reference, but it is also available on the book’s website for you to copy and run against your SQL engine under the file named create_tables.sql
.
The provided SQL is for a MySQL database. You can change any of the definitions to meet the requirements of a database engine other than MySQL that you may be using.
Example 17.1. Table Creation SQL for Project.
-- -- Table structure for table 'company' -- CREATE TABLE 'company' ( 'CompanyID' int(11) NOT NULL auto_increment, 'Name' varchar(50) NOT NULL, 'Address1' varchar(45) NOT NULL, 'Address2' varchar(45) NOT NULL, 'City' varchar(40) NOT NULL, 'ProvID' tinyint(4) NOT NULL, 'Postal' varchar(8) NOT NULL, 'CountryID' tinyint(4) NOT NULL, PRIMARY KEY ('CompanyID') ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table 'countries' -- CREATE TABLE 'countries' ( 'CountryID' int(11) NOT NULL auto_increment, 'Country' varchar(60) NOT NULL, PRIMARY KEY ('CountryID') ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table 'events' -- CREATE TABLE 'events' ( 'EventID' int(11) NOT NULL auto_increment, 'EventTypeID' int(11) NOT NULL, 'EventDate' date NOT NULL, 'EventTime' time NOT NULL, 'PeopleID' int(11) NOT NULL, 'Notes' text NOT NULL, 'Followup' date NOT NULL, PRIMARY KEY ('EventID') ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table 'eventtypes' -- CREATE TABLE 'eventtypes' ( 'EventTypeID' int(11) NOT NULL auto_increment, 'EventType' varchar(45) NOT NULL, PRIMARY KEY ('EventTypeID') ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table 'people' -- CREATE TABLE 'people' ( 'PeopleID' int(11) NOT NULL auto_increment, 'FName' varchar(45) NOT NULL, 'LName' varchar(45) NOT NULL, 'CompanyID' int(11) NOT NULL, 'Phone' varchar(14) NOT NULL, 'Mobile' varchar(14) NOT NULL, 'Email' varchar(45) NOT NULL, PRIMARY KEY ('PeopleID') ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table 'users' -- CREATE TABLE 'users' ( 'UserID' int(11) NOT NULL auto_increment, 'Email' varchar(64) collate latin1_general_ci NOT NULL, 'Password' varchar(64) collate latin1_general_ci NOT NULL, PRIMARY KEY ('UserID') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
This chapter introduced the project that we build in the next chapter. It described the structures of the system, introduced the Zend Framework, and provided the SQL create statements for the underlying MySQL database. Next you will be putting all these pieces together to actually build the small application.
18.118.31.67