How should i design that database in asp.net?

Question

Hi, i am e newbie programmer and i want to improve a "student program" for schools. In the program i want to add lesson information. For example:

Student X
Lesson name    1st exam    2nd exam    3rdexam
mathematics    80          70          80
history        70          70          70
...

I have a database that called KFS with three tables.

Identity
RecordID | firstname | lastname | address | city

Lesson
LessonID | name | description

Lessondetail
DataID | RecordID | LessonID | lessonname | firstpoint | secondpoint | thirdpoint

I can already show list of students in a GridView, but I also want to show the selected student's points.

Is my database enough for this application? If yes, how can i design that relationship and show in gridview? Or should i modify my database?

Answer 1

My first thought when looking at your database design (and it could do with editing to make more clear) is: Will a LessonDetail always have 3 exams? Or could it possibly have more at some time in the future?

I would be tempted to break it up into something like this:

LessonDetail(DetailID, DataID, RecordID, LessonID, LessonName)

LessonPoint(DetailID, Point)

That way a Lesson can have as many exam points as you need.

Answer 2

I'll second Dan Diplo's advice about normalizing LessonDetail if it's not certain that you'll always have 3 exams. Unfortunately, that requires a pivot to get your data in the format you want - but that's another question. Also, you have LessonName in LessonDetail - but it should be pulled from Lesson instead.

The ASP.NET side is fairly easy - it's just a standard Master/Details view:

<asp:GridView DataSourceId="dsStudents" runat="server" DataKeyNames="RecordId" />

<asp:DetailsView DataSourceId="dsLessons" runat="server" />

<asp:SqlDataSource ID="dsStudents" runat="server" SelectCommandText= 
 "SELECT RecordID, firstname, lastname, address, city
 FROM Identity
 ORDER BY RecordID"
/>

<asp:SqlDataSource ID="dsLessons" runat="server" SelectCommandText= 
 "SELECT D.LessonID, L.Name, FirstPoint, SecondPoint, ThirdPoint
 FROM LessonDetail as D
 JOIN Lesson as L ON
    D.LessonID = L.LessonID
 WHERE D.RecordID = @r
 ORDER BY D.LessonID"
>
    <SelectParameters>
        <asp:ControlParameter Name="r" ControlID="gvStudents" 
         PropertyName="SelectedDataKey" />
    </SelectParameters>
</asp:SqlDataSource>