Re: attendance DB design

> 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 - 06:25:50 CDT