Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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-Agency
  • Agency-Contacts
  • Contacts-Contacts
  • 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?

-2nd update-

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.

share|improve this question

migrated from stackoverflow.com Jul 27 '12 at 19:30

This question came from our site for professional and enthusiast programmers.

    
Could you post your schema diagram? –  Emmad Kareem Jul 27 '12 at 22:37
    
Sorry it has taken me so long to respond to your comment. I didn't see it! –  jgradw Jul 31 '12 at 19:27
    
Unfortunately, I can't upload the image right now, because I am still a new user -_- I'll just update the main post with some textual schema diagram. –  jgradw Jul 31 '12 at 19:48
    
I guess your questions are more of a business type than database type. Because, from the information you have given here, we don't know which associations are mandatory and which are not and hence we can't tell what you need to create implicitly. I am sure Salesforce has some user guides you could refer to or even better a board where you find more people familiar with this issue. –  Emmad Kareem Jul 31 '12 at 20:34
    
Ok thank you very much! There are parts to this situation where some knowledge of how salesforce handles databases is needed. I have posted this question in the salesforce forum as well, but haven't received a response yet. Thank you so much for your help! –  jgradw Jul 31 '12 at 21:26
add comment

1 Answer 1

I've no experience with salesforce, so this is a general answer from a database-design perspective.

I think you've made your model amazingly over complicated. As it stands, you'll have many circular relationships, the possibility to run into infinite loops when traversing relationships, and the possibility of everything linking to everything else (which kind of defeats the purpose of everything.)

If I've understood the above correctly, the following is a simplification of your entity relationships:

  1. A program has many projects. (1:N)
  2. Projects have many contacts, and a contact can be assigned for many projects. (M:N)
  3. Agencies have many programs, and a program can belong to many agencies. (M:N)

The above are the "direct relationships."

Now with this simplification, you can relate Agencies to Contacts through programs and projects. There needs to be no direct relationship between the two. (For example, Contact John is assigned to Project X, which is part of Program Y, which is administered by Agency Z.) Same for contact to contact, and even agency to agency.

share|improve this answer
    
Thank you so much for your response! I've posted a bit more information in my main post. If you know of any similar database schema, or websites for them, I would be happy to go over them and learn from their relationships! I am grateful for your help. –  jgradw Jul 27 '12 at 21:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.