Hi I'm a hobbiest and have a basic knowledge of access at best.
I'm curious about design recommendations and when you should and shouldn't create an explicit relationship. I am creating a client support database that has a Client table a Support Package table an accommodation stay table and a Support Plan table
a single client can have many support packages over time and in turn a support package can have many accommodation stay records, which represent a term in one of our accommodations during which may support plans can take place.
Client o>>>>>>>>>m Support Package o >>>>>>>>>>m Accommodation stay o >>>>>>>>>>>m Support Plans
In the support plan table I can include an accommodation stay ID linked to the accommodation stay table which in turn includes a supportPackageID linked t the support packages table which includes a clientID linked to the client table, i'd consider this an implicit relationship as the support plan does not directly reference a client but can still be traced to one because of the relationship chain.
alternatively I can explicitly reference the client ID within the support plan then it would be both explicity linked and implicitly linked. to my knowledge this may make certain queries easier but I would prefer the database to be designed efficiently so I guess I am asking what would the best practice be for creating relationships in this respect?
thanks