Devious Database Designs (Day 98)

Total hours: 211.567

Top three:

1) Operator Table Setup

This is important because the operator can access the account in more than one way going forward. For example Google, Facebook, Github etc... We need to be able to correctly link to the same operator each time. Furthermore the unique id per social authenticator is different so the table design needs to be robust enough to account for that without adding a million columns.

I’ve settled on id, oID, provider and core for the providers table:

• Id is just auto incrementing as you’d expect.
• oID has a many to one relationship to the operators table.
• provider is an integer mapping that we get from another table. (Integers are much faster to compare to than strings like ‘google’ and ‘facebook’). However there’s another table here that does the actual mapping which makes the SQL queries more readable w/o having magic numbers in the queries.
• core this is the magic unique thing per provider. This allows us to condense and move the actual logic for what goes here and how it’s processed up to a higher level.

2) Reviewing Phoenix and other frameworks for data

I’m looking at which provider for server side. I’m gonna be needing to switch out the backend. A small amount of downtime is alright for the starting area, but over time the downtime should become negligible.

3) Planning SQL Queries

It’s been a while since I was in the raw SQL world so I’m needing re reeducate and understand what’s here and how it works. Especially the joining across multiple tables.