CODEWITHSUNDEEP
sql-server-2008entity-framework
Oliver
Oliver

Reputation: 11607

Representing Trees in SQL Server?

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?

Upvotes: 2

Views: 496

Answers (1)

AdaTheDev
AdaTheDev

Reputation: 147234

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

Upvotes: 2

Related Questions