Representing Trees in SQL Server?

Question

I want to create a system of categories and sub categories for my website. Taking the StackOverflow tag system as an example, I want to represent a hierarchy like:

ProgrammingConcepts
    Functions
    FlowControl
        Branching
        Looping
    Datastructures
        List
        Tree

Users should be able to search this tree, so a user could search for FlowControl and get any articles tagged as FlowControl, Branching or Looping.

How should I represent this in my database? The obvious solution is to have a table like this:

Categories
    CategoryId int <PK>
    ParentId int <Nullable>
    CategoryName

I feel that this might be a hard to query on though. I'm just starting to learn asp.net mvc - in particular, the entity framework system seems to work much better than how I have previously worked with the database. Does this play nice with a tree structure?

That'll do. Common Table Expressions let you do recursive queries to walk the tree. — HABO, Feb 29 '12 at 16:35

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

From a db design point of view, that kind of design is the most cross-database compatible - just stick a FK constraint on the ParentId column to reference CategoryId.

In SQL Server-only world, as of SQL Server 2008 there is a hierarchyid datatype specifically to facilitate the storage of hierarchical data.

I'm not sure if it's still the case, but I believe hierarchyid is not supported by Entity Framework though so may need a workaround to go down that route. e.g. HierarchyID in Entity Framework not working

edited May 23 '17 at 12:12

Community

1
1

answered Feb 29 '12 at 16:39

AdaTheDev

142,592
28
206
200

2

HierarcyID won't be officially supported until EF 5 and .net framework 4.5. Although, I used heierarcyID in EF 4. See http://stackoverflow.com/questions/3347860/is-there-a-practical-way-to-use-the-hierarchyid-datatype-in-entity-framework-4 – EBarr Feb 29 '12 at 16:45

Representing Trees in SQL Server?

1 Answers1