This chapter devoted considerable space to discussing database design issues. It has also looked at how VWD supports database development. In this section, you will create the remaining table required by the PPQ Web site. The MenuItems, SizeAndPrice, and Orderstables are provided in the example database. The one missing table is the OrderItemstable.
1.
Open the Database Explorer window (press Ctrl-Alt-S if it is not visible) and right-click on the Tables entry. Select Add New Table from the context menu that appears (see Figure 3-11).Figure 3-11: Database Explorer window
Figure 3-12: Table Designer window
The prefix fkis the accepted way to indicate that this column contains a foreign key. When the rows in two tables are related through key values within specific columns of the tables, the value of the pri- mary key in the parent table row is used in the child table rows that match that parent row. These values in the child table are the foreign keys, and so the column is generally referred to as a foreign key column.
3.
Now continue by defining the remaining columns for the OrderItemstable. These are:Column Name Data Type Allow Nulls
fkMenuItemID int no
ItemSize varchar(50) no
ItemName varchar(50) yes
Quantity int no
LineValue money no
Figure 3-14: Default Value or Bindingproperty
5.
The next step is to specify the primary key for the new table. You will recall from the earlier discussion of the database design that this table requires the first three columns to produce a unique value suitable for the primary key. Click on the square gray row selector button at the extreme left of the first column, (fkOrderID) and hold the Shift key down while clicking on the row selector for the third column (ItemSize) so that the top three columns in the list are selected. Then click the Set Primary Key button on the toolbar (as shown in Figure 3-15), or right-click and select Set Primary Key from the context menu. Alternatively, you can select the Set Primary Key option from the main Table Designer menu. Whichever method you choose, a key symbol appears on the rows to indicate that, together, they form the primary key for this table.Figure 3-15: Selecting the Set Primary Key button
6.
The primary key will automatically ensure that rows will appear in the order of the values in the three columns that form the primary key, unless you specify otherwise in a query. However, sometimes it is useful to create indexes on other columns to speed up processing of queries. For example, if you intend to execute many queries against this table that use the value in theItemNamecolumn, you might want to create an index on it. To do this, right-click anywhere
inside the Table Designer window and select Indexes/Keys from the context menu (as shown in Figure 3-16), or select Indexes/Keys from the main Table Designer menu.
7.
This opens the Indexes/Keys dialog, which shows the index named PK_Table1that was cre- ated automatically when the primary key was set on the table. Click the Add button at the bot- tom of the Indexes/Keys dialog to create a new index named IX_Table1. Make sure that this new index is selected and go to the Columnsproperty in the right-hand window. The first col- umn in the table is selected by default, so click the “three dots” (...) button to open the Index Columns dialog and select the ItemNamecolumn. The default sort order is Ascending, as shown in Figure 3-17, which is what you want. You can change this if you want items indexed in reverse order (for example, with a column containing dates or monetary values).Figure 3-17: Ascending sort order
8.
Click OK to close the Index Columnsdialog and go back to the Indexes/Keys dialog (see Fig- ure 3-18). Notice the Is Uniqueproperty of the new index. Because multiple rows in the table can contain the same value for ItemName(the name of the item ordered), and can also contain NULLvalues (because you did not turn this off in the column designer window), the values will not be unique, and so the index cannot be unique either. The Is Uniqueproperty must be No. For a primary key column, however, or any other column where you specify only unique values can exist, you could create a unique index.Figure 3-18: Indexes/Keys dialog
An index can be created over multiple columns if required. In the Index Columnsdialog, you just add more columns to the grid in the same way as you add columns to the table in the Table Designer win- dow. Click in the empty row below the last Column Name and another drop-down list appears allowing you to select the other columns that will be part of the index. If you go to the Indexes/Keys dialog and select the PK_OrderItemsindex, you will see the three columns that form this index in the Columns
property of the index. You can create as many indexes as you like on a table, but each one has an effect on performance when you add or remove rows in the table. Therefore, you should only create indexes that you will use regularly in queries or when extracting data.