Database Planning Help needed

corruption

I'm working on a website for an organization I'm involved in. I want to basically be able to keep an attendance of people at all the events sponsored by the organization.

I need to keep track of the people in the organization, the activity, and if they were there or not.

In my mind i see the need for at least three databases.

People
fname, lname, address, etc., Perons.ID

Activities
date
activity
Activitiy.ID

Person/Activity
Persons.ID
Activity.ID

The one thing i'm not sure of is how to mark different people off as having completed each activity. This probably needs to be kept track of month by month.

I want to have a form that someone keeps track of the attendance, can go through and check people off after the event as well as adding events. The checking people off would be the hard part. Any help would be great.

bubblenut

Well a person completing an activity and the date/time a person completed an activity are related to both the person and the activity and should therefore go in the person/activity table. I would also think about a different name for the person/activity name, maybe activity_attendee?

So here's what I'd say your database structure should look like

[b]table_name[/b]
[u]primary_key[/u]
[i]foreign_key[/i]
other_element

[b]person[/b]
[u]person_id[/u]
fname
lname
address
...

[b]activities[/b]
[u]activity_id[/u]
name
date
activity

[b]activity_atendee[/b]
[u][i]person_id[/i][/u]
[u][i]activity_id[/i][/u]
completed
date_completed

A couple of judgement calls for you to make, I personally prefer to break down address into parts, so you have a different field for addres line 1, address line 2, ... , town_city, county etc. Also, you could combine date_completed and completed by having date_completed default to null, then you can check if it's completed by checking if date_completed is not equal to null, I prefer to have two seperate fields though. Both point are personal preference though.

corruption

The date of completion will always be the same as the date of the event.

For example

say the group has a meeting, the meeting is on 8/27/04 then it's attendees all completed on that date. So there's not a lot of reason to keep track of that.

Most of the completion's will be attendance related. So that completed date isn't necessary?

Would you still proprose similar structure without the date completed.

corruption

when i get new users, should i enter their id into the activities_attendees and the activity id to that table?

kmunk

I'd go for the before mentioned structure - minus the date_completed.

When getting new users you should create them in the person database.

when creating an activity you should make it possible for the administrator to add persons to the activity.

giving you following scenario:


$query = "insert into activities (name, date, activity) values ('meeting with boss', *Some seconds since 01011970*, 'meeting')";

$result = mysql_query($query);
$activity_id = mysql_insert_id();

for($i=0; $i<sizeof($array_of_atendee); $i++){
  $query = "insert into activity_atendee (person_id, activity_id, 
                   completed) values ('".$array_of_atendee[$i]."'
                   , '".$activity_id."', null)";
  $result = mysql_query($query);
}

the $array_of_atendee is eg. a GET value:

corruption

the end result i want everyone to be able to see the attendance.

So I want to add all of the people.IDs to the third table. Allow the administrator to check people off, via a form and show non-administrative people all of the events and all of the people, but somehow show when a person has completed an event.

So my guess is that i would just add everyone at first to the third table upon adding every new event?

kmunk

You should only add people to the activity_atendee table then when they have completed the activity.

you can show every activity by selecting from the activity table
and you can show every persones by selecting from the person table.


$query_activity = "SELECT name, activity, date FROM activities";
$query_persons = "SELECT fname, lname, adress FROM persons";

//completed for activity id193

$query_atended = "SELECT lname, fname, name, activity FROM activities, persons, activity_atendee WHERE 
activity_atendee.activity_id = activities.activity_id AND 
activity_atendee.person_id = persons.person_id AND 
activities.activity_id = 193 AND completed NOT null";