Now that you have defined your database design, you are going to walk through the steps of creating this database in Access. You are not going to create Access forms or other user-interface tools such as ColdFusion templates but rather are going to consider how to build the tables and relationships
Creating a New Database
The first step in this process is to create a new database file. To do this in Access choose New from the File menu. You will be presented with the New database dialog box shown in Figure 9.1
Select the General tab click the Blank Database icon and then click the OK button to create the database. You will be presented with a File New Database dialog box like the one in Figure 9.2 where you can specify the location and name of the database file
After the database is created you will be presented with the main console for the new database. This window shown in Figure 9.3 has tabbed pages for working with tables queries forms reports macros and modules. These are all described in the Access documentation.
In this appendix you will stick to working with tables
Initially no tables will be shown in this window because the database is empty.
To create a table click the New button. To illustrate how this works you will create the Countries table. After you click the New button you will be prompted to specify one of the following options for creating the table
In.this window you can define fields one per row for your table. For each field you need to specify a field name choose a data type from a drop-down list and then provican optional description of the field. The possible data types are:
Text Text field of 1 to 255 characters
Memo Large text field of up to roughly 64,000 characters
Number Numeric field
Date/Time Field containing a date/time value
Currency Special numeric field for currencies
AutoNumber Automatically incremented numeric field useful for primary keys
Yes/No Boolean value field
OLE Object Field for storing a Windows OLE object (this can be used for including external files from other applications in a database)
Hyperlink Special text field for storing hyperlinks
In the lower part of the window you can define field-specific options, such as default values captions and formats. These options are discussed in detail in the Access documentation but are not directly relevant to the discussion in this appendix. After you define the three fields in your Countries table the Design View window should look something like the one in Figure 9.5
The final step is to define the primary key for the table that, in this case, is the Code field. To do this, right-click in the field and select Primary Key from the drop-down menu. When you do this, a small key icon will appear in the square to the left of the field name.
After you are finished defining the fields for the table, close the window. You will be prompted to indicate whether you want to save the changes to the table; select Yes and you will be ptompt,ed for a table name as shown in the following graphic. Simply type the table name countries and click the OK button.
As you create all the tables in your database their names will appear in the Tables tab of the main console window.
After you have created all the tables, it is time to define the relationships between fields in the tables.
To do this choose Relationships from the tools menu to open the relationship management window. The first time you do this you will be prompted with a dialog box in which you n to select one or more tables to display in the Relationships window.
Select all the tables in your database by holding down the Ctrl key and clicking each table name in the list and then click the Add button. Next click the Close button. You will see the main Relationships window with four small windows displayed each representing one of the tables in your database as shown in Figure 9.6.
Relationships are defined graphically. You can resize and move each table window within the Relationships window. Each table window displays the names of the fields in the table and highlights the primary key in bold.
To create a relationship, you simply drag a field from one table and drop it on another field. For instance to create the link between Info. State and States. Code you would drag Info. State and drop it on States. Code. A window will be displayed for defining the nature of the relationship. For all links in your database the default value is fine . (refer to the Access documentation for discussions of these options). Simply click the Create button to create the link. The link will then be shown as lines between the linked items as in Figure 9.7
The remaining links can be created by dragging Info. Country to Countries. Code Correspondence. Contact to Info. Customer-ID and Correspondence. ResponseTo to Correspondence. ID. To create this last link you need to display two copies of the . window for the Correspondence table. To add another copy of the window simply right-click the background of the Relationships window and choose Show Table from. the drop-down menu. Choose Correspondence in the dialog box click the Add button and click Close. To create the link drag from Correspondence. Response To in one of the windows to Correspondence. 10 in the other.
The end results are shown in Figure 9.8.
After all the links are created simply close the window. When prompted to save the changes click the Yes button.
Where Do We Go from Here?
Now that you know how to create databases for use with your ColdFusion applications the next chapter will look at how the CFQUERY tag is used with the SQL database query language and how it interacts with your databases.
In particular you will learn the basics of SQL, how to manipulate database records and how to display the results of database query. You will also learn to.use Cold Fusion Studio’s SQL Builder, which can allow you to graphically build SQL queries without needing to remember all the details of SQL syntax. You will also look at the CFfRANSACTION tag, which provides advanced capabilities for maintaining data integrity when using a database in a multi-us~r application