In: Computer Science
You have a table for a membership database that contains the following fields: MemberLastName, MemberFirstName, Street, City, State, ZipCode, and InitiationFee. There are 75,000 records in the table. What indexes would you create for the table, and why would you create these indexes?
Students should recognize that each table should have a primary key field that is indexed, and given there is no good primary key field candidate in the existing list, students should add one such as MemberID or MemberNumber. Given people are often sorted by their last name, an index MemberLastName would probably be very helpful. Given there are 75,000 records in the table, there are bound to be two or more members with the same last name, so an index on MemberLastName plus MemberFirstName would probably be very helpful. If several queries and reports are created by State, or City within State, ZipCode, or any other combination of address fields, those fields and combinations are also candidates for indexes. Finally, if several queries and lists are created based on the InitiationFee in ascending or descending order, that field is another index candidate. In all cases, the database administrator should set up tests to check the performance of common activities, queries, and processes both before and after the indexes are created given indexes themselves require overhead to create and maintain.
Tables need indexed primary keys like MemberID; MemberLastName and FirstName combo index aids sorting. Address and InitiationFee indexing enhances query speed.