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:

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.

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.

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.

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.

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