During my recent projects, I came across few issues related to lookup v/s master-detail relationships.
Ideally Master-Detail relationships are best when the detail side of the relationship cannot exist without the master record. The additional benefits are:
1. Record level security is controlled by the parent (OWD).
2. When the parent record is deleted, the child records are deleted automatically.
But there are a few caveats we need to consider.
1. Due the OWD being controlled by the parent, you could run into issues when the detail object records should not be shared with the owner.
An example: The master object represents a user’s human resources profile and one of the detail objects represent the performance ratings of the user. This belong to the user but should not be seen by the user. So a look up relationship is better in this case.
2. If field history tracking is required, then we cannot use the standard history tracking report for the detail object as the standard field history report is not available for detail objects. In this case, once again we will need to use a lookup relationship.
Note: In the case of a master-detail relationship, you can still use the APEX data loader to export history tracking records for your detail object. (You will need to select “Show all objects” in the data loader.