Simple Phoenix LiveView App: Planning DB Schemas

Last episode we installed and configured Phoenix LiveView and setup up a minimal interactive app that captured keyboard events.

This time we'll plan out schemas, look at the relatively complex associations used in a large content site like Alchemist Camp, as explained in the earlier series, and then decide on a good schema structure for this newer, simpler app.

Visualizing the schema relations

This site, Alchemist Camp, has many types of similar content such as, articles, epidodes and podcasts. Comments themselves also have many of the same properties. There are certain behaviors which should work in a uniform behavior across all the content types. Users looking at a tag for "Deployment" are very likely to be interested in information about the topic regardless of whether it's in a podcast or a screencast, for example.

In order to achieve flexible, uniform relationships like this, Alchemist Camp uses the following structure:

And it works great! There is a downside though. There's an extra layer of indirection for every single piece of content on the site. If I want tag and like information for a given article, it's necessary to get the entity first and then use its many-to-many relationship with topics to get the tags and do the same thing again using the article's entity's many-to-many relationship with users to get the likes.

Comments are even a bit more complex since they a relationship with an entity in order to keep track of their own likes, notifications, etc and a relationship with the entity of whichever piece of content they were posted on. It's very flexible once set up, and makes it trivial to allow comments on anything in the app with an entity ID, including other comments... but it's one more line to add to SQL queries.

Simplified relations for a simpler site

Since our new simple Phoenix LiveView site, Reactor, will only have a single top-level content type and comments we can eliminate the abstract schemas and get to any relation with a single join table. In our case that one type of content will be podcasts, but for most projects, it would probably be articles as in the diagram below:

Filling out the schemas

We'll likely add live chat later, but for now the two top level Phoenix Contexts will be Accounts and Content. The schemas within will be the following:

Reactor

• Accounts
  • User
    • name (unique)
    • email (unique)
    • is_verified
    • website
    • password_hash
    • password (virtual)
    • password_confirmation (virtual)
• Content
  • Podcast
    • title (unique)
    • subtitle
    • audio_url
    • notes_md
    • notes_html
    • is_published
  • Comment
    • podcast_id
    • user_id
    • is_published
    • is_flagged
  • Topic
    • name (unique)
  • PodcastTopic
    • topic_id
    • podcast_id
  • PodcastLike
    • user_id
    • podcast_id
  • CommentLike
    • user_id
    • comment_id

With that in place, it will be quick to generate our contexts, schemas, controllers, views and templates with Phoenix generators when we start on Reactor's core app logic.