I am helping to create a database via salesforce for storing information about Non-profit organizations.
It has primarily 4 entities in the schema: Agency, Program, Project, and Contact, where Agency is at the top of the hierarchy, Program is next, etc.
- Many-to-many relationship from Agency to Agency
- Many-to-many relationship from Agency to Contact
- Many-to-many relationship from Contact to Contact
- Many-to-many relationship from Agency to Program
- One-to-many relationship from Program to Project
- Many-to-many relationship from Project to Contact
We have created junction objects for the relationships, and our current strategy is to have a series of queries on each of the object pages to display the correct information, as well as have an option to create a new association manually.
However, we are concerned about updating our database when certain new associations are made. For example, if an Agency to Program association is made, we update all the contacts under that program to be associated with that agency. However, do we update all the projects under that program to be associated with the Agency?
Here is a list of our direct relationships which create implied relationships. If you have any comments or feel any further associations should be created, any help would be appreciated! Thank you so much
- Direct Relationships -> Implied Relationships
- Agency-Program -> Agency-Agency & Agency-Contact (& Agency-Project ????)
- Agency-Project -> Agency-Agency & Agency-Contact & Agency-Program
- Program Project -> Agency-Project
- Program-Contact -> Agency-Contact
- Project-Contact -> Program-Contact
--- UPDATE --- Thank you so much for your expedient response! I believe you could be right that our entity relationships have some redundancies. However, each contact must be under an agency in this database, as an Agency could have no programs under it at the moment. That is another thing this database needs to handle: deletion. But anyway, thankfully the Agency-Agency, Agency-Contact, and Contact-Contact relationships are all done for us by salesforce, so no need to worry about those.
Just a quick overview on the way salesforce works with databases: Their queries work through relationships, as opposed to joins. So in order to have a many-to-many relationship, I would need to create a junction object which associates entities. With the (1:N) relationship though I simply create a master-detail-relationship, called for example ProgramProjectAssociation, between Program and Projects. Then when I perform a query, I can say:
[SELECT Program__r.Name FROM ProgramProjectAssociation__c WHERE Project__r.Name =="Feed The Homeless"]
And then I will get a list of all programs that are affiliated with the Feed The Homeless project.
So for each page of the entities, for example, say Agency Z's page, we want to display all the Agencies, programs, projects, and contacts that agency is affiliated with. If an association needs to be made between Agency Z and Program Y, then the user can simply create that association. However, if the association between Agency Z and Program Y is made, then what other associations are made indirectly? Should Project X under Program Y be displayed on Agency Z's page as well? What about all contacts under Project X, should they be displayed under Agency Z as well?
Here is a list of the schema diagram. In this, I am going to include Affiliation and Relationship objects. These were already created, but they show our (m:n) relationships between agency-agency, contact-contact, and agency-contact associations. Important fields for the relationship object are "related contact", and "reciprocal relationship". Important fields for affiliation are "contact (Master-detail)", "Agency (master-detail)", "program", and "project":
- (1:n) contact to contact
- (1:n) contact to agency
- (1:n) agency to contact
- (1:n) agency to agency
- (1:n) contact to relationship
- (1:n) relationship to relationship
- (1:n) contact to affiliation
- (1:n) agency to affiliation
- (1:n) project to affiliation
- (1:n) agency to AgencyAgencyAssociation
- (1:n) AgencyAgencyAssociation to AgencyAgencyAssociation
- (1:n) agency to AgencyProgramAssociation
- (1:n) program to AgencyProgramAssociation
- (1:n) program to ProgramProjectAssociation
- (1:n) project to ProgramProjectAssociation
- (1:n) agency to AgencyProjectAssociation
- (1:n) project to AgencyProjectAssociation
Sorry that it is textual. My rep points are too low to upload an image. If there is any more information you need about the schema, such as fields for the objects, please let me know.