Database Schema Review

If you prefer to start with a blank slate, feel free to skip this page.

Final snapshot of diagram:

image

Without much knowledge of database schemas, if you just pay close attention, you'll see that that the person_id relationship (foreign key) is not correctly defined on 3 tables: apikeys, apps and roles.

auth-erd-person_id-fk-incomplete

This has caused me/us no end of pain while trying to add new features ... auth/pull/231

So, in addition to dramatically simplifying the database schema, I will make sure that all the Ecto relationships are well-defined so that we don't run into annoying constraint errors in the future.

If we start by deleting the tables that we don't need, we immediately simplify the ERD:

image

If we manually edit the diagram to include the person_id links (foreign keys):

auth-erd-tables-removed-person_id-edited

It becomes clearer what data "belongs" to a person. But we can immediately spot something that incomplete/incorrect: who does a group "belong" to? 🤷‍♂️

Obviously it's "unfair" to pick holes in a feature that is incomplete. But it's "broken" and we (I) need to learn from it. 💭