A fully functioning DB application

Introduction

The purpose of this example is to provide a simple but fully functioning customer management application. The Systems Requirements of our system are the following:

A generic application user must be able:

• To store, manage, and retrieve information about customers. The information must be about the name, email, nationality (Italian or guest), and assurance level (low, middle or high) of any given customer;
• To browse customers list
• To select a customer from the list for showing, editing its data or, eventually, delete its record from the DB
• To add a new customer record to the DB

First of all, we need to define the Database Design to store and manage customers. In our system it is very simple, just the single table customer below:

--
-- Table `customer`
--
CREATE TABLE `customer` (
  `customer_id` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `email` varchar(100) NOT NULL,
  `nationality` varchar(4) DEFAULT NULL,
  `assurance` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- PK for table `customer`
--
ALTER TABLE `customer`
  ADD PRIMARY KEY (`customer_id`);

--
-- AUTO_INCREMENT for table `customer`
--
ALTER TABLE `customer`
  MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

As you can see in the SQL code above, the customer table design stores the name, email, nationality, and assurance level (listed in system requirements). It also defines an incremental primary key, required by DB normal forms (no duplicate tuples). Always remember of taking care to be compliant with DB design principles when building a database.

Due to the simplicity of the system, we made a rapid "System Analysis" for deciding its GUI Design capable of covering all previous system requirements. So, we decided to provide two user scenarios, or better two web pages, for the purpose.

• One for providing the browsing and, then, a customer selection
• The second for providing the showing or editing of a customer record previously selected from the browsing, as well as to delete it, or for adding a new one.

Finally, we provide the System Design to organize the Implementation of the system, by using two Web MVC Assemblies: CustomersManager for browsing customers and CustomerRecord for customer data manipulation. Because the system is very simple we do not have the need for any Subsystems Design