Display heirarchical data C#

Question

I am relatively new to programming so be gentle...

I am trying to develop a page to display student/class results by subject/teacher.

Some classes have groups which contain students and some have only students. A subject is thought by one or more teachers. This may change in the future and I want to keep the design as flexible as possible (i.e. subjects might be broken into modules or into individual test results, classes might be further grouped by year etc)

I want to display the end results in a grid that I can expand and roll up to average out the results (class on the Y and subject on the X).

My though it to have 3 database tables as follows:

**class_structure**
id (int) PK
description (varchar)
parent_id (int) FK to id

The highest level would be the class (with no parent id) and the lowest level would be the student.

**subject_structure**
id (int) PK
description (varchar)
parent_id (int) FK to id

The highest level being the subject, lowest being the teacher

**results**
id (int) PK 
class_structure_id (int) FK
subject_structure_id (int) FK
date (datetime)
reult (int)

What do you think of the above structure? I believe this gives me flexibility going forward?

In my asp.net app then I query the highest class structure level and average out all the results below that (am I better to do this averaging in a sproc in the DB or use say LINQ in the app?)

This would give me back a set of results at the highest level.

Then I would need to display this is a table (i am thinking of just creating the table html in C#?) against the subject classes (i.e. create all the headers first and then try to place the result in the correct column (i am not sure how to do this though)).

<table border="1" padding="2">
 <tr>
  <td></td>
  <td></td>
  <td colspan="2" align="center">Subj 1</td>
  <td colspan="2" align="center">Subj 2</td>
 </tr>

 <tr>
  <td></td>
  <td></td>
  <td>Teacher 1</td>
  <td>Teacher 2</td>
  <td>Teacher 1</td>
  <td>Teacher 2</td>
 </tr>
 <tr>
  <td rowspan="2">Class 1</td>
  <td>Student 1</td>
  <td>90</td>
  <td>55</td>
  <td>75</td>
  <td>100</td>
 </tr>
 <tr>
  <td>Student 2</td>
  <td>40</td>
  <td>95</td>
  <td>65</td>
  <td>39</td>
 </tr>
</table>

Then when the user clicked on the first class, this would expand out and show all the groups (if any, students if not) (pass the class_structure_id of the clicked cell to the same method/sproc and get those results) and then redraw the table with an extra column.

Is this a good way to go about achieving what I am trying to do?

Thanks for your time.

Answer 1

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.

Answer 2

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.

Answer 3

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)

Answer 4

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.