Logic for creating an attendance system in c# and any database(help needed with the db schema)

I have to create an attendance management system of our college.. .the Requirements are.. there are 6 subjects

1. every subject has a table for every month..
2. every class conducted is recorded and is numbered from 1 to n..and the present candidates are given the count if present...
3. at the end of every month, total classes taken by all 6 subjects are summed up...and total classes attended by each student is summed up and a percentage is calculated...(total present classes)/(total classes )*100...
4. there are 120 students...

please help me create an efficient way to create the databases... note.... the application should allow you to view through all the absent classes he did not attend for reference..(so every absent class must be recorded)

My..attempts:

1. one way i tried was: create tables for each subject...then 6 tables required

cons: *every day must be a column and the copy of 6 tables should be created every month. i.e 6 sub*12 months =72 tables every year also a extra classes can be taken per subject...so if a subject is taken twice on the day,3 cases arrive--> present for both,absent for both,present for only one..*

2..the second method I've tried is:

create a table for each stu开发者_如何学运维dent with the same db schema ..(with subjects as columns ) con:

12 students will require 120 tables

:P Any bright ideas guys...would really help if you give me ideas how to construct a db schema for this kinda application.......

thankyou.....

Students table:

StudentID   int identity PK
LastName    string(20)
FirstName   string(20)

Classes Table:

ClassID     int identity PK
ClassName   string(50)
SubjectID   int FK to Subjects table (not shown)
TermID      int FK to Terms table (not shown)

Students to Classes table:

STCID       int identity PK
ClassID     int FK to Classes table
StudentID   int FK to Students table

Attendance Table:

AttendID    int identity PK
STCID       int FK to Student To Class table
Date        date/time

PK means Primary Key. FK means Foreign Key (it associates to the Primary Key in other tables).

The Students to Subjects table connects the students with their subjects. For each subject that a student is assigned, add a record to the Students to Subjects table. For each date that a student attends a particular subject, add a single record to the Attendance table.

First of all, you definitely do not need to duplicate tables for each month or student. That's madenss.

You can calculate the month from the date, so just storing a date solves that.

Here's my initial reaction.

You need tables for students, subjects, registrations, classes and attendence.

Registrations table:

ID
Student_ID
Subject_ID

Classes table (use a different name) shows all of the valid class dates (required to calculate attendance %):

ID  
Subject_ID  
Class_Date

Attendance table:

ID  
Student_ID  
Class_ID (refers to the ID field in the class table)

(modify the names to suit your conventions)

This way attendance contains a record of every appearance by every student in every class. You can determine which classes each student missed in each subject by SQL queries.

All of the logic you described can be handled with this structure unless I missed something. Keep your data model clean and let your code do the work.

Here's some SQL DDL based on @nycdan's answer but using natural keys rather than artificial (surrogate?) keys because doing so allows for improved referential integrity i.e. Attendance can and should reference Enrollment to ensure attendance cannot be logged for a student who is not registered on that course. I've also changed a few names. I'm only including the most salient attributes. :

CREATE TABLE Students (student_ID INTEGER NOT NULL UNIQUE);

CREATE TABLE Courses (course_ID CHAR(6) NOT NULL UNIQUE);

CREATE TABLE Enrollment 
(
 course_ID CHAR(6) NOT NULL
    REFERENCES Courses (course_ID), 
 student_ID INTEGER NOT NULL
    REFERENCES Students (student_ID), 
 UNIQUE (course_ID, student_ID)
);

CREATE TABLE CourseSchedules
(
 course_ID CHAR(6) NOT NULL
    REFERENCES Courses (course_ID), 
 class_date DATE NOT NULL, 
 UNIQUE (course_ID, class_date)
);

CREATE TABLE Attendance
(
 student_ID INTEGER NOT NULL, 
 course_ID CHAR(6) NOT NULL, 
 class_date DATE NOT NULL, 
 FOREIGN KEY (course_ID, student_ID)
    REFERENCES Enrollment (course_ID, student_ID), 
 FOREIGN KEY (course_ID, class_date)
    REFERENCES CourseSchedules (course_ID, class_date), 
 UNIQUE (course_ID, class_date, student_ID)
);