• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 3. Relationships: Your Key to Da... > Practical Examples: Establishing the...

Practical Examples: 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, “What Every Developer Needs to Know About Databases and Tables.”

  • tblClients to tblProjects— You need to relate tblClients and tblProjects in a one-to-many relationship based on the ClientID field. You must enforce referential integrity to ensure that the user cannot add projects 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 the user deletes a client. Instead, you want to prohibit the deletion of clients who have projects by establishing referential integrity between the two tables.

  • tblProjects to tblPayments— You need to relate tblProjects and tblPayments in a one-to-many relationship based on the ProjectID field. You must enforce referential integrity to ensure that the user cannot add payments 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 the user deletes a client. Prohibit the deletion of clients who have payments by establishing referential integrity between the two tables.

  • tblProjects to tblTimeCardHours— You need to relate tblProjects and tblTimeCardHours in a one-to-many relationship based on the ProjectID field. You must enforce referential integrity to ensure that the user cannot add hours 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 the Jet Engine deletes the associated hours if the user deletes a project.

  • tblProjects to tblTimeCardExpenses— You need to relate tblProjects and tblTimeCardExpenses in a one-to-many relationship based on the ProjectID field. You must enforce referential integrity to ensure that the user cannot add expenses 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 the Jet Engine deletes expenses if the user deletes a project.

  • tblEmployees to tblTimeCards— You need to relate tblEmployees and tblTimeCards in a one-to-many relationship based on the EmployeeID field. You must enforce referential integrity to ensure that the user cannot add time cards 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 the user deletes an employee, you do not want the Jet Engine to delete all the employee's time cards.

  • tblEmployees to tblProjects— You need to relate tblEmployees and tblProjects in a one-to-many relationship based on the EmployeeID field. You must enforce referential integrity to ensure that the user cannot assign projects 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 the user deletes an employee, you do not want the Jet Engine to delete all the employee's projects, which is generally not desirable.

  • tblTimeCards to tblTimeCardHours— You need to relate tblTimeCards and tblTimeCardHours in a one-to-many relationship based on the TimeCardID field. You must enforce referential integrity to ensure that the user cannot add time card hours 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 the user deletes a time card, you want the Jet Engine to delete the corresponding hours.

  • tblTimeCards to tblTimeCardExpenses— You need to relate tblTimeCards and tblTimeCardExpenses in a one-to-many relationship based on the TimeCardID field. You must enforce referential integrity to ensure that the user cannot add time card expenses 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 the user deletes a time card, you want the Jet Engine to delete the corresponding expenses.

  • tblExpenseCodes to tblTimeCardExpenses— You need to relate tblExpenseCodes and tblTimeCardExpenses in a one-to-many relationship based on the ExpenseCodeID field. You must enforce referential integrity to ensure that the user cannot add time card expenses 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 the user deletes an expense code, you do not want the Jet Engine to delete the corresponding expenses.

  • tblWorkCodes to tblTimeCardHours— You need to relate tblWorkCodes and tblTimeCardHours in a one-to-many relationship based on the WorkCodeID field. You must enforce referential integrity to ensure that the user cannot add time card hours 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 the user deletes a work code, you do not want the Jet Engine to delete the corresponding hours.

  • tblPaymentMethods to tblPayments— You need to relate tblPaymentMethods and tblPayments in a one-to-many relationship based on the PaymentMethodID field. You must enforce referential integrity to ensure that the user cannot add payments 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 the user deletes a payment method, you do not want the Jet Engine to delete the corresponding payments.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint