开发者

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

  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. 开发者_运维技巧
  3. Create a table for reasons and a table for arrivals:
    • 1 initial + 1 column/week
    • 40 rows per table (or more)
    • 2 tables
  4. 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)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜