When you index a column, R:BASE records the location of every row in the column. When you use the column to search for or sort data, R:BASE can find values in the column more quickly. The columns EmployeeID and CustomerID are key columns and will be used to look up data from the Customer and Employee tables as well as the Flights table. We automatically indexed these columns when we defined them as primary and foreign keys (constraints). We don't need to define additional indexes for them as R:BASE uses the constraint index to speed data retrieval and to link tables.
In addition to looking up data by employee and by customer, we might want to look up all the flights scheduled for a particular date or range of dates. To speed up this process, we'll index the FlightDate column in the Flights table.
To define an index:
| 1. | In the "Database Explorer" window, click the "Tables" option from the Group Bar. |
| 2. | Highlight the Flights table. |
| 3. | Select "Design Table". |
| 4. | Click the "Keys/Indexes" option. |
R:BASE displays currently defined indexes and constraints for the selected table. The primary key and two foreign keys defined for the Flights table are displayed.
| 5. | Click the "New Key" option. |
R:BASE displays a sorting menu listing the columns in the Flights table. Indexes can be defined to use multiple columns. The multi column index works better when your where clauses have columns in the same order as the index. If you plan to use single column where clauses then single column indexes would be return faster results. We are building a single column index.
| 6. | Select "FlightDate". |
You are prompted to select the sort order for the index.
| 7. | Click the "Descending" radio button and select "Add". |
Descending was chosen as users are more likely to search newer records as the database grows, thus speeding up and searches on the FlightDate column.
| 8. | Click the OK button. |
The "New Key/Index" dialog box is displayed.
| 9. | Select "Index". |
The dialog box displays options applicable to an index. Indexes must be named.
| 10. | Enter "Flights_FlightDate" as the name for the index within the "Enter name for this Index" field. |
| 11. | Click the OK button. |
The index definition is complete. You should the following keys and index defined for the Flights table.
| 12. | Click the "Save" option. |
| 13. | After confirming that the changes were saved, select the "Close" option. |
Well done! You have just defined a relational database using RBASE. Now we'll look at the database files and structure.
