What if you had the following:

objHierarchy [ID, Name] - custom object, Name could be something like 'Ben Franklin Five and Dime' - with one corp hqtrs, branch offices, stores - each are Accounts in your hierarchy.

objHierarchyMembers [ID,Name,objHirearchy(lookup),Account(lookup), ParentID] - Accounts in a hierarchy.

Account(ID, Name, .... , objHierarchy(lookup))

So, given an account, you know the ID of the objHierarchy, so you can run a query

select Account__r.name, ... from objHirearchyMembers where objHierarchy__r.id = :account.objHierarchy__r.id

Now, the trick is to maintain the objHirearchyMembers object. This should be easily done with triggers, but the kicker is finding the objHierarchy and knowing when to create a new objHirearchy on the fly - that's what I envision objHierarchyMembers.ParentID is for (when you create an Account that has a ParentID and that ParentID has no hits in objHirearchMembers - is that when you create a new hierarchy on the fly?).

Also, 'pruning' would be a sticky wicket. Suppose the company sells off the western branch office & all the stores under it. There would be a new hirearchy created and the objHierarchyMembers would have to be targeted to isolate the structure, then a different or new objHierarchy.ID would have to be assigned.

It sounds like the kind of thing you'd want to work through manually with about 7 accounts first, then try it in a sandbox.

I've got a similar (maybe even more complicated) situation that I'm prototyping in Visual Force & it's awesome (Visual Force, ... though my scheme isn't too bad either ;)

Thanks for the detailed description.  I'll file this away for future reference. 

What I ended up doing was implementing an S-Control that loops through the ParentId of the Account record until it reaches null to find the top of the hierarchy, and then got the information I needed from the main/parent Account:

var currentRecordId = '{!Account.Id}';
var parentRecordId = '{!Account.Id}';

var continueLoop = true;

while(continueLoop)
{ 
    var SearchString = "Select ParentId, Name From Account Where Id = '" + parentRecordId + "'";
    var queryResult = sforce.connection.query(SearchString);
    var records = queryResult.getArray('records');
    if(records.length == 0)
    {
        continueLoop = false;  //no records returned
        continue;
    }
    else if(records[0].ParentId == null)  //at the top of the hierarchy, get the Name
    {
        parentRecordName = records[0].Name;
        continueLoop=false;
        continue;
    }

    var account = records[0];
    parentRecordId = account.ParentId;
}

 

It works for what I need it to do.

Since this is the "Apex Code Development" board, may I suggest that you move these queries into a custom apex webservice method that allows you to get the information from salesforce in one round trip rather than n?

Another way to accomplish this, again with Apex, is to create a custom lookup back to account called "ultimate parent" that is set on each record as it is created based on this value from the related parent object.