score:3

Accepted answer

1:) Hierarchical data should not be displayed in a <table>, use nested <ul> or <ol> instead. Tables are for tabular data.

2:) Since c# is an object oriented language, I would not reference the objects in the tree by an Id. Why not add a collection of child objects as a member a sample code pattern would be:

class TreeElement 
{
 public TreeElement Parent {get;}
 public IEnumerable<TreeElement> Children{get;};
 public AddChild(TreeElement element }
 public bool IsRoot { return Parent == null; }
 public bool IsLeaf { return Children.Length == 0; }
 public bool IsBranch {return !IsRoot && !IsLeaf; }
}

normally in a tree you don't want to do a parent lookup. So if not needed leave all the parent references to avoid useless complexity ;) Try to insert stuff in the database in an object structure like above.

3:) Key lookup according to your model will go eventually very slow in SQL. You 'll have to loop trough data and use if/else statements in your query On SQLTeam is a great sample on how to make that very fast with the use of an extra lineage column.

4:) Use linq to query and generate the results for your data.

score:0

I would suggest having the following tables:

Person
 - Person_ID (PK)
 - Type (student/teacher etc)
 - Name etc...

PersonGroups
 - PersonGroup_ID (PK)
 - Description etc..

Subjects
 - Subject_ID (PK)
 - Description etc...

Classes
 - Class_ID (PK)
 - Subject_ID (FK)
 - Description etc...

PersonClassMembership
 - Person_ID (FK)
 - PersonGroup_ID (FK)
 - Class_ID (FK)

PersonGroupMembership
 - PersonGroup_ID (FK)
 - Person_ID (FK)

With this it is flexible enough to add more memberships such as new teachers or students to a class/group.

This is by no means the best way to do it or the 'right' way, its simply to get you going and thinking about normalising the database to make creating queries that give you the results more easily.

score:0

I don't agree with your table design. Putting mixed data in one table with recursion is really a bad idea. Don't do this unless you need to store tree-like data in DB. According to your question, a better design:

  • table Students
  • table Teachers
  • table Classes
  • table SubjectTypes(type_id,description) English,math,sports,etc...
  • table Subjects(class_id,teacher_id,subject_type_id)
  • table StudentDistribution(stu_id,subject_id)
  • table Score(stu_id,subject_id,score)

score:1

If you can use Jquery in your application, then consider using the following Jquery plugin, which has got lot of features..

http://www.jstree.com/

JsTree is absolutely free (licensed same as jQuery – under the terms of either the MIT License or the GNU General Public License (GPL) Version 2) - As stated in the jstree website.


Related Query

More Query from same tag