Representing paths in SQL Server database (ASP.NET MVC)

If you are talking paths like in filesystem paths, then you might want to start thinking about about a trees and a hierachical system.

In essence, you will have an additional table in a form named like Category or something, which is self referencing with a ParentId. With this you can iterate along your caegories until, there is no parent or child item, depending on the direction of your query and build a path with the result of your query. This of course can get ugly, if your bookmarkings have more then 1 category. You will want to get some additional validation into this.

The following code is acutally from an article on the web, which I used myself, but I can't find the article again, to quote it as the source.

public class Category
{
    public int CategoryId { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; }
    public virtual Category Parent { get; set; }
    public virtual ICollection<Category> Children { get; set; }
}

public class CategoryMapping : EntityTypeConfiguration<Category> 
{
    public CategoryMapping() 
    {
        HasKey(x => x.CategoryId);

        Property(x => x.CategoryId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        Property(x => x.Name)
            .IsRequired()
            .HasMaxLength(255);

        //Parent is optional, because root node has no parent
        HasOptional(x => x.Parent)
            .WithMany(x => x.Children)
            .HasForeignKey(x => x.ParentId)
            .WillCascadeOnDelete(false);
    }
}

References:

• http://sqllessons.com/categories.html
• http://www.sitepoint.com/hierarchical-data-database/
• Entity Framework Code First Self Referencing Parent Child with Payload