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

Chapter 4. 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 the relationships you need to set up for the tables included in the Time and Billing database:

  • 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 clients who do not exist. 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 projects that do not exist. 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 projects 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 projects that do not exist. There is no need to enable 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 projects that do not exist. There is no need to enable Cascade Update Related Fields, because the ProjectID field 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 employees who do not exist. 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 employees who do not exist. 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 are deleted.

  • 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 time cards that do not exist. 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 time cards that do not exist. 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 expense codes that do not exist. 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 work codes that do not exist. 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 a payment method that does not exist. There is no need to set Cascade Update Related Fields, because the PaymentMethodID field 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.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


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