Database needs to be restructured.

[lluixhi]

Currently we have 11 different tables in order to implement functionality on the website, there is duplicated data, in some cases they are insufficient, and they add complexity that does not get us closer to shipping this website by Summer.

• assigned_workshifts
  • contains information about who is assigned a workshift
• categories
  • used for types of workshift.
• day_quiet_hours
  • contains information on the quiet hours for a specific day
• preferences
  • associates categories and users with ranks
• quiet_hours
  • units and entries in day_quiet_hours.
• units
  • information about different houses
• users
  • lots of user data
• weekly reports
  • information on how reports for units are created.
• workshift_assignments
  • information on assigned workshifts
• workshift_selling_limits
  • value that specifies when how early workshift sales must happen in the market
• workshifts
  • information on what workshifts exist.

Proposed new structure:

Data tables (Mostly static):

• workshifts (information about individual shifts)
  • name
  • cat_id
  • hour_length
  • hour_val
• members (information about individual members)
  • name
  • email
  • password
  • hour_balance
  • workshift_manager (T/F)
  • workshift_date_interval
  • selling_date_limit
  • blown_hour_multiplier
• categories (Workshift Categories)
  • name
• day (Used by member availability and weekly master schedule)
  • day
  • start_time
  • preference (-,+,0,x, null for workshifts)

Joining tables:

• weekly_workshift_schedule (The permanent shift assignments for the week)
  • workshift_id
  • day_id
  • member_id
• weekly_member_prefs (Join between a user and days for weekly preferences)
  • day_id
  • member_id
• member_workshift_pref (The ranking given by members for workshifts)
  • user_id
  • workshift_id
  • rank
• shifts (All shifts that have happened or will happen -- generated by workshift_date_interval)
  • date
  • completed (T/F)
  • weekly_workshift_schedule_id
  • workshift_market (T/F)
  • user_id