how to set up database

Question

I’m creating an app that tracks attendance of players in amateur football teams. And I’m just not sure how to set up the database. A bit of info: every user has a team and should only see the events within his team per season.

These are the main things:

• Users

• Events

• User stats (for later)

• Events

  • Training / match / social
  • Date
  • Time
  • Subject / opponent
  • Attendance

Main questions:

• Do I make an event table per team per season? Or do I put all events in one big table for every user?
• What is the best way to handle the attendance? They should be able to choose from
  • Present
  • absent
  • Fan

Thanks in advance!

Answer 1

I would do it this way:

User
    userid PK
    Name
    
Team
    teamid PK
    TeamName
    
UserInTeam
    userid FK
    teamid FK
    
Event
    eventid PK
    home_teamid FK
    visiting_teamid FK
    eventtypeid FK
    Date
    Time
    Subject / opponent
    Attendance
    
Event type
    eventtypeid PK
    EventType
    
Attendance Type
    attendanceid PK
    AttendanceType
    
UserAtEvent
    userid FK
    eventid FK
    attendancetypeid FK

Content of Event Type

1   Training
2   Match
3   Social
    

Content of Attendance Type

1   Present
2   Absent
3   Fan

For Event, home_teamid and visiting_teamid can be NULL if the event is not a match

The application will show the events based on the team the user is a member of.