A Closer Look: What Joe Doesn't Know
Behind the scenes, Joe has actually touched three different interfaces of the Peachmail application:
The front-end, or graphical user interface (GUI). This is what Joe sees when he loads up the Web site. It formats the display area, provides the visuals to Joe's interactions, and sends the commands that Joe triggers to the back-end. It can be anything from an HTML form on a Web page to a Flash program to a Java applet.
The back-end, or server-side interface. It performs actions based on the front-end's feedback, manages the data that's sent between the front-end and the data storage, and is the "traffic-cop" between incoming and outgoing data. The back-end is typically created using server-side scripting languages, such as Perl, ASP, PHP, or ColdFusion, but can also be developed using more large-scale languages such as Java and C++.
The data storage interface, such as a text file or a database. This is the data warehouse. The most common databases tend to be Oracle, or MSSQL Server for large-scale operations, and mySQL, postgreSQL, or Microsoft Access for smaller operations.
For our email client application, we'll use Flash MX for the front-end, Java for the back-end, and Microsoft Access for the database. The data being sent and received between the front-end and back-end will be in XML format to accommodate Flash MX's XML data handling routines, as you'll see later. Now let's take a look at what we want our application to do.
Bread and Butter
To quickly summarize the steps Joe went through at Peachmail: he created an account, logged into Peachmail, and sent an email. Using his actions as the base, we can quickly develop a blueprint (Figure 7.1) for what we want our application to do.
Figure 7.1 The blueprint for our email client application.
This is the ideal functionality for the application. Note that to keep things simple, the following limitations are applied:
Peachmail does not include account management services, that is, account modification and account deletion.
Peachmail's address book services do not include an edit function.
Peachmail is limited to using only one folder to track email; there is no organization tool to allow creation of custom folders and moving messages around.
Messages are only stored for incoming email.
Attachments can only be received, not sent.
After we discuss creating the database, we'll look at login and registration, address book services, and email services. We leave it for you, as an exercise, to extend the functionality of Peachmail and include the missing pieces that make up the ideal model in the blueprint.
Now that we have a good idea of what we are going to create, we can focus on the data required for this application to work.
The Data Definition
The first item on the blueprint is creating the account. Because the application is for email management, Joe had to enter his email address, his SMTP server, and his POP server, in addition to the obligatory username and password. We'll lump all this information into a table called Users, and add a UserID number to uniquely identify each registered user. Figure 7.2 shows the fields in the Users table.
Figure 7.2 The Users table.
When Joe logs into Peachmail, the application checks against the Users table to ensure that Joe is a registered Peachmail user. If Joe makes any changes to his account, the changes are saved to this table.
Once Joe is logged in, he has access to his address book, which is simply a list of names and email addresses that Joe can use to send out email. We need to create a table called AddressBookEntries to store names and email addresses, and add AddressBookEntryID to uniquely number each entry. In addition, we add a UserID field to link to each user, such as Joe, so that we know to which user each address book entry belongs. Figure 7.3 shows the fields for this table.
Figure 7.3 The AddressBookEntries table.
The core of Peachmail is the email service, which Joe uses to receive and send out email. For this, we need a table called Messages in which to store the email messages that are received. Figure 7.4 shows the fields in this table.
Figure 7.4 The Messages table.
Body stores the message, and ReceivedDate records the date when the message is received. Title contains the message subject, and New is used to flag which messages are unread. UserID links the message to a specific user, and MessageID is a number to uniquely identify each message.
To keep messages organized, we want to allow Joe to create folders to store his messages, so we have a simple table called Folders. Figure 7.5 shows the fields for this table.
Figure 7.5 The Folders table.
Each folder links back to the user through the UserID, and its FolderID is used to link a folder to messages in the Messages table.
Lastly, we want to deal with attachments from email messages, so we have a table for Attachments, and Figure 7.6 shows its fields.
Figure 7.6 The Attachments table.
FileName and FilePath combine to identify where the file is stored on Peachmail's server. MessageID links back to a specific message in the Messages list, and AttachmentID is a number to uniquely identify each attachment.
Figure 7.7 The ERD for the Peachmail data definition.
Table 7.1 Peachmail Data Definition
TABLE NAME |
FIELD NAME |
FIELD DATATYPE |
Users |
UserID |
AutoNumber |
Username |
Text |
|
Password |
Text |
|
EmailAddress |
Text |
|
POPServer |
Text |
|
SMTPServer |
Text |
|
AddressBookEntries |
AddressBookEntryID |
AutoNumber |
UserID |
Number |
|
Name |
Text |
|
EmailAddress |
Text |
|
Messages |
MessageID |
AutoNumber |
UserID |
Number |
|
FolderID |
Number |
|
Title |
Memo |
|
Body |
Memo |
|
New |
Yes/No |
|
ReceivedDate |
Date/Time |
|
|
|
|
Folders |
FolderID |
AutoNumber |
UserID |
Number |
|
FolderName |
Text |
|
|
|
|
Attachments |
AttachmentID |
AutoNumber |
MessageID |
Number |
|
FileName |
Text |
|
FilePath |
Text |
The next logical step is to create the database using our data definition.