Database Design Help

[clifweb]

I am going to design a Java application that connect to a database to store member registration to the organization I attend too. In My Design I would Like to store the attendance of each member of each day. Done once a day every day except Wednesday. Create Reports. and print labels to send letters by post.

I have made some fields that I need and divided them a bit.

Table: Parents:
ID: AutoNumber
Title -> Sir, Maddem , etc
Surname:
Father's Name:
Mother's Name:
House Number or House Name:
Street Name
Locality
Post Code
Telephone/Mobile

Table: Child:

ID: AutoNumber
Name:
DOB:
School
Health
Other Information
Class --> An other Table with the list of Classes

I need to link the Parent with Child: One Parent many Children
I need to link The Each Child with A History of Attendance:

Example:
Name Surname : 2007: January == attended 15 times
" : 2007: February = attended 10 times
...
Name Surname : 2008: January == attended 8 times
" : 2008: February = attended 4 times
...

[dshaw256]

Make sure you add some common keys. Example: Your parent table has an ID per record, but your Child record doesn't have a parent ID. Add ParentID to Child. Then you can do things like "select list of fields from parent inner join child on parent.ID=child.ParentID where ...."

I would assume that a Child can attend multiple classes, that that Class can be attended by multiple children. To do that, you need to get rid of Class from the Child table and use an intersection table:

Table: Attendence
childID int,
classID int

Assuming the class table has an ID field, then you can join kids to classes with:

select whatever fields you want from child inner join attendance on child.id=attendance.child.id inner join class on attendance.classid=class.id where ...

You can start at class and get list (or count) of students, start at student and get a list or count of classes, etc.

General rule: one-to-one or one-to-many relationships are done by putting the one-record's id in the many-record's table. Many-to-many relationships require an intersection table that holds keys to both records.

[clifweb]

Each Child will have one class per year.
I.E. in 2007 he has Class A in 2008 he will have Class B.

The part I need help most is how to keep the attendance for each year of each Students

[clifweb]

Anyone has some suggestion how to implement the attendance?

Should I link the attendance with the class or with the child?
Since the class is changed every year?

Can some give me a structure:
design of the attandance

[lmo]

Just an idea ...

Class could have like
CLASS-TERM

where CLASS-TERM (s) might be like YEAR or like YEAR-FALL, YEAR-WINTER, ...

and each Class record would refer to CHILD

You could then find out all present and past classes of CHILD by looking it up in CLASS

[dshaw256]

The general problem of having CHILD referred to in a CLASS record, is that you'd have to recreate the CLASS record for every child. Now, if it's just a name, no big deal. But if you have other information in there (teacher, meeting times, room number, etc.) then you'd be duplicating all that information for every CHILD that attended the class. That's why I am pushing you toward using intersection tables. Each child is represented by a CHILD record, each class (name, location, season, meeting time(s), etc.) is represented by a CLASS record, and an ENROLLMENT table, maybe, has childID:classID pairs to make the connections. Then you can have any number of children per class, any number of classes per child.

But if every child attends only one class at a time, you could put the class ID in the child record. That design LIMITS you to one class per child at a time, so if your requirements change in the future, you will have some interesting rework to do.

For attendance, I'd do a table that had childID, classID, date and time of attendance. Add one record per child when they attend their class. Humble opinion.

[clifweb]

The class will contain only the name no other details are required.

[clifweb]

Here are a basic design without the class and attandance:
If Somew can add to this I think I could understand better