organize assistance in mysql: tables VS columns

Question

Reason

I am organising a system to keep the track of assistance/punctuality in a music band weekly. We are around 40 people, and I would like how should I organize it. I need to save 2 things, hour of arrival, and reason. I also have in the database all members that were part of the band, and I mark if they are currently active. I don't want to erase data from any member, but new members may get into the band.

Options

1. Create a table for each user, and a entry for each day, referencing what time did him arrive and the reason:
   • 3 columns
   • 1 row/week
   • 40 tables (or more)
2. Create a table for reasons and a table for arrivals:
   • 1 initial + 1 column/week
   • 40 rows per table (or more)
   • 2 tables
3. Create a table for each day:
   • 3 column per table
   • 40 row/table (or more)
   • 1 table/week

So... which is more efficient?

Answer 1

Try something like this:

BandMembers Table:

BandMemberId (integer), FirstName (varchar), LastName (varchar), DateInserted (Date/Time), Active (boolean/bit)

Attendance Table:

AttendanceId (integer), BandMemberId (integer), Arrival (Date/Time), ReasonId (integer)

UPDATED WITH REASON TABLE:

Reason Table:

ReasonId (integer), Reason (varchar)

This way you can join Attendance table to BandMember table on BandMemberId.

---

UPDATED 5/25 NEW REQUIREMENTS:

Since you need to store information about each rehearsal/meeting, I'd do it like this:

BandMember Table:

BandMemberId (integer), FirstName (varchar), LastName (varchar), DateInserted (Date/Time), Active (boolean/bit)

EventTable:

EventId(int), EventName (varchar), EventDate (datetime), EventTypeId (int)

EventType Table:

EventTypeId (int), EventDescription(varchar)

BandMemberEvent Table:

BandMemberEventId (int), BandMemberId(int), EventTypeId(int), TimeArrived(date/time), ReasonId (int), EventId (int)

Reason Table:

ReasonId (int), Reason(varchar)