tblServices.CustomerID ---> tblCustomers.CustomerID
tblServices.OperatorID ---> tblCustomers.CustomerID (to record this relationship, you'll need to add tblCustomers to the relationships window a second time because you are creating a separate relationship. This does not duplicate anything. Access will suffix the second instance of the talble with "_1" so the table names can be unique in the relationship window.)
tblServices.ServiceTypeID---> tblServiceTypes.ServiceTypeID
- since I eliminated the Operator table and suggested that operators are equivilent to customers, you may want a was to identify which people in the customer table are operators too. The OperatorFlg should be defined as yes/no with no as the default. When you add an operator, set the value to yes. This will allow you to separate the two groups of people with criteria in a query if necessary.
I would think enforcing referential integrity would
- yes, enforcing RI is critical to good design. You should also check the cascade delete option for the first relationship but NOT for the others. In the first case, if you delete a customer, you want all his services records to also be deleted. Cascade delete will do that for you. You will need to add your own business rule in the BeforeDelete event of the form that prevents operators from being deleted because that would cause all their related services records to be deleted. You don't want the operator record to control when the services are deleted, you want the customer record to control it.
If you are getting error messages when you try to enforce RI, tell us what they are. If the tables are empty you should not have any errors if the table keys are properly defined. If there is data in the tables already, it will need to have valid values in the foreign key fields or Access will refuse to create the relationships. You'll need to fix the data or delete it.
You don't do anything to specifically define a foreign key. When you create a relationship, "foreign key" is the description applied to the field in one table that "points" to the primary key in the related table. So the CustomerID in the services table is a "foreign key" and the CustomerID in the customer table is a primary key. So foreign, in this case, means exactly what you would expect. Something away from its "home".
The primary/foreign key pairs must have the same data types. However, autonumber is really a data type of long integer so that's what most foreign keys will be defined as. You can't have two autonumbers related to each other. It doesn't make any sense. So the pk will be autonumber for most of your pk's and the related fk's will be long integer.
As to names. I prefer to use the same name because I think it makes the relationships more obvious without having to open the relationship window all the time. There are exceptions though, and your app has one of those eceptions. Because I merged the operator and customer tables and both types of customer records are involved in a services relationship, only one of the columns in the relationship could be called CustomerID. The other needed a different name and so I used OperatorID which describes the use of the second customer record in the relationship.