- Shopping Bag ( 0 items )
Ships from: Camden, DE
Usually ships in 1-2 business days
In this chapter, you will take a brief look at the process of designing a database and implementing it. The subject of designing relational database models is complex and worthy of one more book of its own. We can't hope to do justice to the subject beyond the smallest glimpse into it; a whole body of theory relates to the design of efficient databases. For most small databases, the best way to learn design is simply by doing it.
You will design a small contact-management database in this chapter. You will not be creating the user interface to the database, which could be done using ColdFusion, but rather will look at the requirements of the project, and then walk through the decisions that go into designing the back-end database that would support the application. Finally, you will walk through the implementation of the model you have designed using Microsoft Access. The process of implementing database design differs from database to database, and especially between desktop databases and full-scale client-server database systems. Still, the basic approach used inModel
For the purposes of our exercises in this section, you will walk through the design and implementation of a database to act as the back end in a simple contact-management system. The system will need to provide the following functionality:
In designing this database, you need to follow several steps:
1. Gather requirements: In this stage, you look at the data you are tracking and any special issues that need to be addressed in this data.
2. Determine tables: Based on the requirements, you need to look at the specific data-base tables that you will create.
3. Assign primary keys: Each table needs a primary key. You will look at the data to be stored in each table to see whether there is a logical primary key, and, if not, consider how to create one.
4. Define columns: For each table you need to define the fields (or columns) to appear in the table, the data type and other limitations on the type of data stored in the field.
5. Make relationships: In the final stage, you specify relationships between tables. The whole idea behind relational database systems is that they leverage relation-ships between sets of data to create complex models of information.
Gathering Requirements and Determining Tables
We have already outlined the basic database requirements in our specification of application requirements. For instance, you know that you need to track personal information about each contact as well as maintain a historical log of correspondence with pointers to actual electronic documents of correspondence. One central issue is left from that definition: ensuring that "state codes, telephone country codes, telephone area codes, and country names are entered in a standardized fashion."
The standard tactic for doing this in a relational database is to maintain separate lists of countries, states, and telephone codes, and then, rather than include the state name or phone code in the personal information, simply point to an item in the list. Because all personal information records simply point to items in these country and state lists, you can ensure that every record uses a consistent spelling for country and state names, and that any required spelling changes need to be done only one place: in the separate list of countries or states.
This does raise one issue, though: It is reasonable to maintain a separate list of all countries and their telephone codes, and all states and provinces in the U.S. and Canada. However, it seems unwieldy to maintain a list of states and provinces in all countries in the world. Therefore, you will also need to provide a way to handle U.S. and Canadian addresses slightly differently from those of other countries. For U.S. and Canadian addresses, the user should be able to select an entry from the list of states and provinces, but for other addresses, the user should be able to manually enter a province or state.
This is a common tactic in many Web sites that ask for addresses in forms. They pro-vide a drop-down list of all states and provinces in Canada and the U.S. This list, however, includes one entry along the lines of Outside the U.S. and Canada and then provides a blank text field that the user can fill in when they choose this option on the drop-down list. This type of form simply reflects the way in which data may be structured in the underlying database.
The question, then, is how all this translates into tables in your database design. You will need the following databases to store the information required by the application:
Info A table for tracking personal information
Countries A list of countries and their associated telephone codes and international two-letter codes
States A list of states and provinces in the U.S. and Canada and their associated two-letter codes for addressing envelopes
Correspondence A table for tracking correspondence with all contacts
Assigning Primary Keys
For each of these tables, you need to define a primary key. The primary key for a table is a table field that can be used to uniquely identify any record in the table without reference to information in the table. This means that the primary key value needs to be unique for each record in the table.
In some cases, data being tracked in the table may be suited to being the primary key. For instance, an employee identification number will be unique for each employee and therefore can serve as the primary key. However, in many tables, a field will not be suited to being the primary key. In these cases, you need to create an additional field in the table for the purposes of storing an arbitrary primary key.
When using such an arbitrary primary key, different approaches can be taken in deciding what value to use for the key. The simplest approach is to use an automatically incrementing number for the primary key; that is, with each new record, the value of the primary key increments by one, and new records can never have a primary key smaller than any used before. Therefore, if a record in the middle of the table is deleted, its primary key is not freed up for reuse by a new record.
Other approaches include deriving the primary key from information in the table based on a formula that is guaranteed to provide a unique value. This approach has advantages in terms of being able to derive the key for any record and provides a non-arbitrary relationship between the key and the data in the record. For the purposes of small databases, if the data in a table doesn't offer a possible primary key, it is probably easiest to use an automatically-incrementing number for the primary key...
|Ch. 1||Creating Your First ColdFusion Template||3|
|Ch. 2||Passing Data between ColdFusion Templates||15|
|Ch. 3||Getting Data from a Database||43|
|Ch. 4||Creating and Manipulating Variables||75|
|Ch. 6||Writing Expressions||97|
|Ch. 7||Controlling the Flow of Your Templates||115|
|Ch. 8||Including Outside Code||129|
|Ch. 9||Creating Databases and Tables||143|
|Ch. 10||Using CFQUERY and SQL to Interact with the Database||159|
|Ch. 11||Using Advanced Query Techniques||221|
|Ch. 12||Grouping, Nesting, and Formatting Outputs||269|
|Ch. 14||Working with ColdFusion Data Structures||307|
|Ch. 16||Validating Form Data||419|
|Ch. 17||Implementing the ColdFusion Web Application Framework||443|
|Ch. 18||Implementing Error Control||507|
|Ch. 19||File Management||543|
|Ch. 20||ColdFusion Studio||559|
|Ch. 21||Sending Mail||595|
|Ch. 22||Reading Mail||613|
|Ch. 23||Using the HTTP Protocol||639|
|Ch. 24||Accessing FTP Servers||659|
|Ch. 25||Working with LDAP Servers||675|
|Ch. 26||Evaluating Dynamic Expressions||685|
|Ch. 27||ColdFusion Scripting||695|
|Ch. 28||Scheduling Events||705|
|Ch. 29||Implementing a Search Engine||721|
|Ch. 30||Application Security||739|
|Ch. 31||Building ColdFusion Custom Tags||763|
|Ch. 32||Including External Objects||795|
|Ch. 34||Using the CFREGISTRY Tag||827|
|Ch. 35||ColdFusion Administration||839|
|Ch. 36||ColdFusion for Linux||881|
|App. A||Installing ColdFusion||889|
|App. B||ColdFusion Tag Reference||897|
|App. C||ColdFusion Function Reference||955|
|App. D||Differences between ColdFusion and Traditional Server-Side Programming||1013|
|App. E||SQL Functions and Data Types||1019|
|App. F: Additional Resources||1031|
Posted July 8, 2001
The fact that I use it as the textbook for my 600 over students means this book is good for beginners. It contains a lot of examples and explanation. Very good explanation on session management, etc. I prefer this book to Ben Forta's The ColdFusion 4.0 Web Application Construction Kit. However, if you want something very advanced, then go for Ben Forta's Advanced ColdFusion 4 Application Development (it's the Volume 2 of his ColdFusion book). In fact, I think it's more worthy to get 1 book of Mastering ColdFusion 4.5 than 2 volumes of Ben Forta's book.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Posted May 1, 2001
I have just finished reading this book and found it very confusing! If you are new to coldfusion this book is okay, however if you already understand a little about cold fusion do not bother. This book was very poorly organized, leading you in developement of a data source you never use, giving examples of applications (one of the most important aspects of coldfusion) and never really follows through with explianations of why something was done. I found this book to only add to my frustration level! You can certainly tell that the writes know what they are talking about, however they can not transform their knowledge into a legiable format!Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Posted August 16, 2000
I have been developing in Coldfusion for about six months and picked this book up mainly as a reference. I have never needed to look farther than this book for any questions or problems that I've had. I own two other Coldfusion books and have yet to put a crease in their covers. I'm amazed at the diversity of information contained here. It covers everything from the simplist of functions to the much more advanced.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Posted June 24, 2000
Posted June 23, 2000
This book is an excellent resource for any ColdFusion developer. The presentation and subject matter are outstanding. A few sections on some of the more advanced topics are a little confusing to read, but overall this is a must-have book. I own Ben Forta's ColdFusion books and this book is an excellent cross-reference and filler for information missing from those books and for topics related to ColdFusion 4.5.1.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.