organize assistance in mysql: tables VS columns
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
- 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)
开发者_运维技巧 - Create a table for reasons and a table for arrivals:
- 1 initial + 1 column/week
- 40 rows per table (or more)
- 2 tables
- Create a table for each day:
- 3 column per table
- 40 row/table (or more)
- 1 table/week
So... which is more efficient?
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)
精彩评论