Dynamic contacts database

What you are proposing is a good solution for your requirement of M:N products to categories and hierarchical categories.

To avoid exposing yourself to numerous updates: You need to do two things to ensure that you don't have a lot of updates in your intersection table.

First, you need to be sure that your categories have a stable, persistent primary key.

Second, you need to link food items to leaf categories. Don't join Cherry to Red, Healthy, Fruit and Food - just join it to Red and Healthy. Your nested sets take care of all of the secondary (and higher level) associations.

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.