[RESOLVED] database structure

cbesh2

I am wanting to create an application that tracks attendance for meetings and need help in designing my database. I figured I would have a table for the people who will attend, but I'm not sure what would be there best way to set up the table for the dates/attendance.

Should I do the following?

People - peopleID, peoplelname, peoplefname
Date - dateID, date
Attendance - attendanceID, peopleID, dateID

If a person attended the meeting, then an entry is made in the attendance table using peopleID and dateID. But if I did it that way, how do I show who did not attend the meeting for a selected date? I couldnt' do a query where date!=070206 because it would show all the other dates as well.

So is it best to create an entry ONLY if they attend the meeting, or should everybody have an entry and the value would change if they attend? For example, if I did not attend I would have an entry for "no" and "yes" if I did.

Any comments? Thanks for your help in advance.

devinemke

what you have here is a classic many-to-many data relationship. you have multiple people who can attend multiple meetings. you need a table for each and then a third table to link them together.

people
person_id (PK)
first_name
last_name

meetings
meeting_id (PK)
date_time
location

attendance
attendance_id (PK)
person_id
meeting_id

cbesh2

Yes, I was thinking of something similar to the way you have it strucutred. But if I only enter the times the people attend the meeting, how will I show who was not there on a particular date? If my query is

SELECT person.person_id WHERE date.date != 070206

then it will return other dates, wouldn't it?

devinemke

let's say we want to see everyone who was NOT at metting #123:

SELECT * FROM people WHERE person_id NOT IN(SELECT person_id FROM attendance WHERE meeting_id = 123)

cbesh2

Oh, I learned something new. Thank you so much!

cbesh2

Okay, I guess it didn't work after all.

It's giving me an error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT person_ID FROM attendance WHERE meeting_ID=7)' at line 1

I've tried researching the error to correct but I can't figure it out. Any suggestions on what I'm doing wrong?

devinemke

you must use mySQL 4.1 or higher to use subqueries