Database Relationships overview appreciated

database-designschema

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.

Best Answer

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:

A program has many projects. (1:N)
Projects have many contacts, and a contact can be assigned for many projects. (M:N)
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.

Related Solutions

ER Modeling; multiplicity basics – one to many or many to many

I think your design could be stated more formally as:

One company has many projects. 
One project belongs to one company.
One project has one or more activities.
One activity has exactly one activity type (prototyping, design).
One activity belongs to exactly one project.

So, if that's true, you have the following tables:

COMPANY
CompanyID
....

PROJECT
ProjectID
....

ACTIVITY_TYPE
ActivityTypeID
Name (e.g. "Prototyping", "Design")

ACTIVITY
ActivityID
ProjectID
ActivityTypeID
StartDate
EndDate
...

Sql-server – Table design for child tables with multiple many-to-many relationships

I'd recommend creating a table which encompasses subnets, VLANs and VPLSs; you can then have a simple self-join table. If these three concepts each have a significant number of unique fields, you can use vertical partitioning and create a sister table for each of the three. Using pseudo-code:

* Networks
NetworkID PK

* Locations
LocationID PK

* SubNetworks  -- This concept encompasses SubNets, VLANs, and VPLSs
SubNetworkID PK,
NetworkID  NOT NULL REFERENCES Networks

* SubNetworkLocations
SubNetworkID NOT NULL REFERENCES SubNetworks,
LocationID   NOT NULL REFERENCES Locations,
PRIMARY KEY (SubNetworkID, LocationID)

* SubNets
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* VLANs
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* VPLSs
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* SubNetworkRelationships
SubNetworkA NOT NULL REFERENCES SubNetworks,
SubNetworkB NOT NULL REFERENCES SubNetworks,
PRIMARY KEY (SubNetworkA, SubNetworkB),
UNIQUE (SubNetworkB, SubNetworkA)  -- For efficient querying from the other direction

As with any many-to-many table, be sure your sproc for inserting into SubNetworkRelationships checks for the existence of both { A, B } and { B, A } before inserting a new row. You can use check constraints to prevent a sub-network from relating to itself.

In this model, there's no RDBMS-level enforcement of the rule "if two sub-networks are related, they must have all of the same locations." I'm going to go out on a limb and assume that all such tied-together sub-networks must belong to the same Network, too. If you really want this:

* Networks
* Locations

* SubNetworkGroups
SubNetworkGroupID PK,
NetworkID         NOT NULL REFERENCES Networks

* SubNetworkGroupLocations
SubNetworkGroupID NOT NULL REFERENCES SubNetworkGroups,
LocationID        NOT NULL REFERENCES Locations,
PRIMARY KEY (SubNetworkGroupID, LocationID)

* SubNetworks
SubNetworkID PK

* SubNetworkGroupMemberships
SubNetworkGroupID NOT NULL REFERENCES SubNetworkGroups,
SubNetworkID      NOT NULL REFERENCES SubNetworks,
PRIMARY KEY (SubNetworkGroupID, SubNetworkID)

* SubNets
* VLANs
* VPLSs

A group of SubNetworks belong to one Network but zero or more Locations. A SubNetwork can belong to multiple groups. You'd still need to enforce that the various groups a sub-network belongs to all have the same locations, so I'm not sure this buys you much.

Best Answer

Related Solutions

ER Modeling; multiplicity basics – one to many or many to many

Sql-server – Table design for child tables with multiple many-to-many relationships

Related Question