current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
2

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.

share|improve this question
    
Seems right to me. –  rwilliams Nov 19 '10 at 19:44
    
This is a good approach. The design is called Adjacency List. Common Table Expressions in SQL Server make this a relatively easy approach for the problem you're trying to solve. For more about representing hierarchies in databases see this question: stackoverflow.com/questions/4048151/… –  orangepips Nov 19 '10 at 21:38

4 Answers 4

up vote 2 down vote accepted

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: Self Join Cap

share|improve this answer
    
CTE <==> Common Table Expression? –  John Nov 19 '10 at 19:47
    
@John: Yes, thanks. Answer updated. –  Paul Sasik Nov 19 '10 at 19:49
    
Thank you for that! –  John Nov 19 '10 at 20:14
up vote 4 down vote

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.

share|improve this answer
    
Hierarchy ID may have limited tool support, but once you learn the syntax it provides some very useful query options, especially in the context of a hierarchy with unlimited levels. –  Paul Keister Nov 19 '10 at 21:18
up vote 2 down vote

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.

share|improve this answer
up vote 0 down vote

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.

share|improve this answer
    
So, you would have hierarchical Account table and have Users table that would have foreign key refering to Accounts table. –  Nickolodeon Nov 19 '10 at 20:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.