Home > Articles > Data > Access

  • Print
  • + Share This
This chapter is from the book

Examining Indexes and Relationships

The field that joins two tables on the one side of a one-to-many relationship must be a Primary Key field or must have a unique index so that referential integrity can be maintained. If the index on the one side of the relationship is not unique, there is no way to determine to which parent a child record belongs.

In Access 2002, it is not necessary to create an index for the field on the many side of the relationship. Access 2002 will create an internal index for you. If you do create an index on the many side of the relationship, make sure that you set the index to Yes (Duplicates OK); otherwise, you will have a one-to-one, rather than a one-to-many, relationship.

Establishing the Relationships Between the Tables Included in the Time and Billing Database

In this example, you'll establish some of the relationships you need to set up for the tables included in a hypothetical time and billing database. If you would like to build the relationships yourself, open the database that you created in Chapter 2.

  • tblClients to tblProjects—tblClients and tblProjects need to be related in a one-to-many relationship based on the ClientID field. You must enforce referential integrity to ensure that projects cannot be added for nonexistent clients. There is no need to set Cascade Update Related Fields because the client ID that relates the two tables is an AutoNumber field in tblClients. You do not want to enable Cascade Delete Related Records because you do not want any billing information to change if a client is deleted. Instead, you want to prohibit the deletion of clients who have projects by establishing referential integrity between the two tables.

  • tblProjects to tblPayments—tblProjects and tblPayments need to be related in a one-to-many relationship based on the ProjectID field. You must enforce referential integrity to ensure that payments cannot be added for nonexistent projects. There is no need to set Cascade Update Related Fields because the ProjectID that relates the two tables is an AutoNumber field in tblProjects. You do not want to enable Cascade Delete Related Records because you do not want any payment information to change if a client is deleted. Prohibit the deletion of clients who have payments by establishing referential integrity between the two tables.

  • tblProjects to tblTimeCardHours—tblProjects and tblTimeCardHours need to be related in a one-to-many relationship based on the ProjectID field. You must enforce referential integrity to ensure that hours cannot be added for nonexistent projects. There is no need to set Cascade Update Related Fields because the ProjectID that relates the two tables is an AutoNumber field in tblProjects. Enable Cascade Delete Related Records so that hours are deleted if a project is deleted.

  • tblProjects to tblTimeCardExpenses—tblProjects and tblTimeCardExpenses need to be related in a one-to-many relationship based on the ProjectID field. You must enforce referential integrity to ensure that expenses cannot be added for nonexistent projects. There is no need to set Cascade Update Related Fields because the ProjectID that relates the two tables is an AutoNumber field in tblProjects. Enable Cascade Delete Related Records so that expenses are deleted if a project is deleted.

  • tblEmployees to tblTimeCards—tblEmployees and tblTimeCards need to be related in a one-to-many relationship based on the EmployeeID field. You must enforce referential integrity to ensure that time cards cannot be added for nonexistent employees. There is no need to set Cascade Update Related Fields because the EmployeeID that relates the two tables is an AutoNumber field in tblEmployees. You do not want to enable Cascade Delete Related Records because, if an employee is deleted, all the employee's time cards are deleted.

  • tblEmployees to tblProjects—tblEmployees and tblProjects need to be related in a one-to-many relationship based on the EmployeeID field. You must enforce referential integrity to ensure that projects cannot be assigned to nonexistent employees. There is no need to set Cascade Update Related Fields because the employee ID that relates the two tables is an AutoNumber field in tblEmployees. You do not want to enable Cascade Delete Related Records because, if an employee is deleted, all the employee's projects would be deleted, which is generally not desirable.

  • tblTimeCards to tblTimeCardHours—tblTimeCards and tblTimeCardHours need to be related in a one-to-many relationship based on the TimeCardID field. You must enforce referential integrity to ensure that time card hours cannot be added for nonexistent time cards. There is no need to set Cascade Update Related Fields because the time card ID that relates the two tables is an AutoNumber field in tblTimeCards. You do want to enable Cascade Delete Related Records because, if a time card is deleted, you want the corresponding hours to be deleted.

  • tblTimeCards to tblTimeCardExpenses—tblTimeCards and tblTimeCardExpenses need to be related in a one-to-many relationship based on the TimeCardID field. You must enforce referential integrity to ensure that time card expenses cannot be added for nonexistent time cards. There is no need to set Cascade Update Related Fields because the time card ID that relates the two tables is an AutoNumber field in tblTimeCards. You do want to enable Cascade Delete Related Records because, if a time card is deleted, you want the corresponding expenses to be deleted.

  • tblExpenseCodes to tblTimeCardExpenses—tblExpenseCodes and tblTimeCardExpenses need to be related in a one-to-many relationship based on the ExpenseCodeID field. You must enforce referential integrity to ensure that time card expenses cannot be added with nonexistent expense codes. There is no need to set Cascade Update Related Fields because the expense code ID that relates the two tables is an AutoNumber field in tblExpenseCodes. You do not want to enable Cascade Delete Related Records because, if an expense code is deleted, you do not want the corresponding expenses to be deleted.

  • tblWorkCodes to tblTimeCardHours—tblWorkCodes and tblTimeCardHours need to be related in a one-to-many relationship based on the WorkCodeID field. You must enforce referential integrity to ensure that time card hours cannot be added with invalid work codes. There is no need to set Cascade Update Related Fields because the work code ID that relates the two tables is an AutoNumber field in tblWorkCodes. You do not want to enable Cascade Delete Related Records because, if a work code is deleted, you do not want the corresponding hours to be deleted.

  • tblPaymentMethods to tblPayments—tblPaymentMethods and tblPayments need to be related in a one-to-many relationship based on the PaymentMethodID field. You must enforce referential integrity to ensure that payments cannot be added with an invalid payment method. There is no need to set Cascade Update Related Fields because the PaymentMethodID that relates the two tables is an AutoNumber field in tblPaymentMethods. You do not want to enable Cascade Delete Related Records because, if a payment method is deleted, you do not want the corresponding payments to be deleted.

  • + Share This
  • 🔖 Save To Your Account