I have a registration/profile table that holds user information for a non-profit that I'm helping out.

They'd like to start recording attendance for meetings/events in a database with web access, and the current script and database is PHP/MYSql.

The problem that I'm having is that the people in the group are generally not around for more than 3-4 years, so the membership is constantly changing.

What I've got so far works because it just keeps a running tally of events attended in each person's profile. However, they'd like to be able to keep track of individual events, especially because if the wrong info is accidentally entered, there's really no way to account for it.

If each event were a row and each member a column, can PHP/ some database dynamically find and add columns? I've been kinda stuck because I know INSERT requires you to know in advance each column name and value.

Any ideas, or am I overlooking something obvious? I myself won't be around the group forever, so I'm trying to make the code as flexible as possible to grow with the group, and so that people can administer the website without knowing code.

Thank you in advance!
~Dan

Any ideas, or am I overlooking something obvious?

Yep

You have separate tables holding the members, events and attendance data.

members table
id, name, etc

events table
id, eventname, dates, etc

attending table
id, memberid, eventid

then you just run JOIN queries to find which members attended which events

thanks! I've been teaching myself specifically to do this project, and it's been difficult to keep track of everything/ remember everything without prior experience.

My only other question is, wouldn't this get a little slow? There are 1+ events per week, and peak membership of the group is ~80... thus adding 80+ rows/event on the third table.

I'll take you at your word, just curious. Thanks for your help!

Originally Posted by dzdrazil

My only other question is, wouldn't this get a little slow? There are 1+ events per week, and peak membership of the group is ~80... thus adding 80+ rows/event on the third table.

Sure - any approach will get slower over time as data accumulates. The method Chris suggested makes use of narrow tables. This means less disc access, especially if you have good indexes. At an elemental minimum, the database has to read entire rows, so those should (generally) have as little data as logically possible, and be joined on demand.

1 event a week with > 100 members and you worry about it being slow???

when it is handling 1000 events a day with 500,000 members, speed may become an issue

silly questions deserve silly answers, i suppose. I'll give it a shot! Thanks all!