Database design help - hierarchical data

So I have a table of user accounts (Users). There needs to be functionality in place for subaccounts.

So for instance, a company named Dunder Mifflin might have an account. The company will have subaccounts, Accounting and Sales. The Accounting account would have subaccounts for Kevin, Angela, and Oscar. And there's no limit on the number of开发者_运维知识库 levels.

My initial idea was to create a table like this:

CREATE TABLE Users
(
    UserID INTEGER,
    ParentUserID INTEGER,
    ...
)

Where a primary account's ParentUserID would just be null, but a subaccount would contain the UserID of its parent.

Is this a good design for this? I don't know of any other way.

---

it is a good design for it. An alternative is to use the HIERARCHID data type to mape the hierarchy, but support for that is limited (reporting, ORM tools etc.).

Actually I use EXACTLY this in a number of setups. There simply is not too many alternatives that are not obviously dump (like having X fields for the hierarchy). I acutally know of no single alternative.

---

That is a good design and you really have little other choice. Read up on CTEs (Common Table Expressions) which will help you query this hierarchical relationships (recursively.)

Recursive querying of hierarchical structures was possible in Sql Server 2000 but is much simplified with CTEs since 2005.

A table that joins to itself will look something like this in your designer:

---

This is called a self-join and, yes, it is the standard way of representing hierarchical data. You're probably going to need to query like this to get something like all of the users associated with Dunder Mifflin.

---

What are subaccounts are used for? Database design is a serious matter. Earlier answers claim that the design you demonstrate is good by definition. When you have hierarchical data, yes, you always have parent ID. However, very often you have some sort of group account to which accounts belong. That would be a more proper place to setup a hierarchy.