Re: attendance DB design

From: mountain man <prfbrown_at_magna.com.au>
Date: Sat, 21 Sep 2002 21:25:50 +1000
Message-ID: <J56j9.37390$g9.106742_at_newsfeeds.bigpond.com>


> We need to design an attendance module for our college project. The
> basic requirement is to record daily student attendance and generate
> reports based on the dates selected (weekly, monthly). We plan on
>
> a. a table with student id as primary key and a column each for each
> day of the academic year.
>
> however we don't think this is a good database design as the table
> would have 1 column for each day which might come close to 365 columns
> for 365 days.
>
> could anyone please suggest a better alternative or is this approach
> practical.
>
> Thanks
>
> Farheen.

How about three tables:

  1. Student Information (student ID, name, address, contact, etc)
  2. Attendance Table (student ID, Date, Status)
  3. Status Table (1=present, 2=absent without reason, 3=sick (cert provided), 4=etc)

To get a record for one student
select * from attendance_table where student ID=X

To get a record for one set of days:
select * from attendance_table where date between Y and Z

Farmer Brown
Falls Creek, OZ
http://www.mountainman.com.au/software/SQLServer/ Received on Sat Sep 21 2002 - 13:25:50 CEST

Original text of this message