Best way to represent hierarchy?

This is probably pretty broad, but I'm looking for the best way to organize something.

What's I'm looking to emulate is something like the military structure, or the structure of any business.

A military unit is organized into squads, platoons, companies, battalions, brigades, divisions, etc...

What I want is a way to organize people in the database, by whatever number of columns or types of columns, so that if I'm assigned assigned to a certain company, there's a way to see if a certain individual is "under" me... they are in a platoon or squad that's under my company. So I will be able to see their data, but not those individuals that are in another squad, platoon, company, etc...

This could be related to a regular company, too. You're the manager and what would be a system to store the data so that I can "view" people in my divisions or sections, but not in other divisions of the corporation.

There will be a huge amount of squads in the database, and each unit you go up, the population gets smaller. Would some kind of bit column work? or a combination of parent-child type columns?

Again, I know it's fairly broad. I'll go into more detail if nec. Hopefully someone can spark some ideas, I haven't had time to think about it.

---John Holmes...

Apart from the classical object_id - parent_id structure, I've seen a pretty interesting structure on searchdatabase.com ... I'll post it later if you are interested, as now I'm in a mess ...

Ufff ok, here are the links:

http://searchdatabase.techtarget.co...i537290,00.html

http://searchdatabase.techtarget.co...x289340,00.html

Please let me now your thoughts, as I'm interested in this subject.

Thanks for the links. I tried looking around on the site before, but couldn't find the articles. I'm reading over them now, hopefully they give me some ideas.

---John Holmes...

I think this is going to work.

At first I didn't think so, though, because although the military has a tree style hiearchy, there can be more than one person in charge of a group. So instead of a simple tree, Bill in charge of George in charge of Sue, I've got Bill, Fred, and Mark in charge of George and Sue in charge of Eric.

In the first example, you'd have

Bill
1 6
|
George
2 5
|
Sue
3 4

I was confused on how to model the second example, since what numbers would I give Bill, Fred, and Mark. Then I realized I could use the same numbers as above.

Bill, Fred, Mark
1 6
|
George, Sue
2 5
|
Eric
3 4

So multiple people can have the same numbers. All of the queries will still work, and if you want to find someone's "partner" or equal, then you just look for people with the same lft and rgt. You would just keep the names in anothe table. You would give each lft-rgt pair a unique id. So 1-6 would be Hierarchy Level 1, or H1. In your "name" table, Bill, Fred, and Mark would all be H1. George and Sue would be H2, which relates to 2-5, etc.

I think this will work and be adaptable enough. I just have to sit down and think about it some more.

Thanks again.

---John Holmes...