DB Simplification

[ayroblu]

The database is generated, complex and unnecessarily normalised.
There are several parts that should be simplified:

1. Recurrence
2. User
3. Points of Interest

My apologies to Eddie for overriding the hard work he's done, I could see the normalisation that's been done to be useful, but typically for under 1mil row tables, and especially early stage, simple is best, its best not to over engineer early

Recurrence

This is one to one with a poi, you pretty much won't have a second occurrence of an event without an edit to the existing, or more likely generating a new one, we give enough flexibility here that if you want to pick any dates, you can.

{
  poi_id: '...'
, type: 'week' // none, week, month, year, day, weekday, custom or whatever else
, repeat: 2
, interval: 1 // every week - could be fortnight, 3 weeks?
, exclude: [{
    poi_id: '...', date: '2017-...'
  }] // Something for later, if events skip a public holiday or similar
, include: [{
    poi_id: '...', date: '2017-...', start_time: '10:00', end_time: '13:00'
  }] // If you just want to select certain dates, most flexibility here
, start_date: '2017-...'
, start_time: '10:00'
, end_time: '13:00'
, days: ['Monday', 'Wednesday'] //Only for if type == weekday
}

This way we don't need to have tables for months or days etc.
We probably won't support multiple times in a day

User

Do we actually have an OAuth solution here? Cause what you're supposed to do is record the response object cause they normally time out after like 10 minutes. Also, that shouldn't be stored on the person table. Should probably rename the table User, it makes more sense for this app.

• Remove OAuth - to implement when we actually implement some OAuth
• Rename table user
• Password should go in user table, just password_hash, bcrypt stores salt in the hash so all good
• Person type -> role (TEXT)
• Person state -> state (TEXT)
• Remove user agent table

Optional: These are unnecessary and just take time to implement

• Move the fail count, last_failure to main table
• Record password failures in another table

POI

• Tag to just a string, not the id, same with category, and state
• Mimetype on the image
• Need more detail on participants (TODO), don't think that area is quite fleshed out enough