How should I structure my DB?

Hi all,

I’m going to do my best to explain my challenge and have included a diagram to add a bunch of context.

I’m building an onboarding tool SaaS. My clients will be able to log into the platform and invite their clients in order to onboard them.

My clients don’t actually create any lessons or tasks. I create these for them. These are available out of the box in the platform. Their job is just to invite their clients and assign whichever lessons are appropriate to their clients.

My main challenge right now is that I’m trying to figure out how I should store lessons/tasks in the db for new users on the platform. Because the lessons are essentially just templates, I only want to give users the ability to view the lessons (i.e. watch videos) and mark things as complete (think Kajabi/Teachable).

I’m not sure how I should store this in the db.

Has anyone run into this before? Any help appreciated :pray:.

I’ve added more context in the diagram below.

You should setup your User thing first

User thing
-user_role (admin, client, end-user; text type)
-user_permissions[list of things](create,update,delete,read-only; text type)
-client_account(Client type)
-end_user_account(End-user type)

Client thing
-contact(User type)
-client_end_user(End-user type)

End-user thing
-contact(User type)
-company(Client type)
-tasks[list of tasks](Task thing)

-any field you need here

Thanks for sharing, Doug.

The challenge I’m encountering though is that I have a hierarchical set of data types (Modules, Lessons, Tasks) that are standard across the entire app (Modules are the category, lessons contain videos, tasks have multiple things that need to be done).

These need to be assigned to every user that’s invited into the platform but cannot be changed by them. I need that method of assignment and a way of tracking when a user marks a lesson and task as complete (in a way that doesn’t impact the platform standard Modules, Lessons, Tasks).

I’m not sure if that makes sense.

If you setup initially sort of like i had posted earlier, it leaves almost everything interop able. Not being able to change something either comes from your apps logic and the roles and permissions you gave someone

Then you just create all your types

A Task
A Module
A Lesson

all of which will be created with a UID that can be referenced back to initial entry but stored in a end-users To-DO list or whatever you wanted to call it

Sorry, my noob brain still seems to be having trouble with this.

The part I’m struggling to understand is how the data types associate a lesson (that’s in the system by default) to a user (with the ability for them to mark as complete).

Maybe it’s clear in your response but I’m having a hard time connecting the dots.

Start with creating a type and naming it Module. Then, create all the fields a module might have.

Then, create a Lessons type. Create all the fields a Lesson might have.

Then, create a Task type. Create all the fields a task might have, plus a conditional field to be able to distinguish from being an ‘open’ task or a ‘completed’ task which will be able to be set with your apps logic.

And start building from there.

1 Like

What you’re getting hung up on is that you have these Module/Lesson/Task things and they are system things that only you/the system creates. They are templates, basically.

And now you are like, wait a minute… how do I assign one of these things to some user without copying it? I only want these to be created by ME!

Well, just make a new thing called an ASSIGNMENT. It would have (for example) fields on it like: Module (a field of Module type), Lesson (a field of Lesson type), Task (a field of Task type). It would have other fields on it of course so we can figure out if the Assignment is complete and stuff, but you’re probably already like, “oh, duh.” We’ll probably have an “Assignee” field of type User on it, too, eh?

And now, I want to assign something to someone, like:

Assign User @keith to the Module “sensitivity training”

Well we create a new Assignment with Module set to the Module for Sensitivity Training and Assignee set to @keith.

Hooray! :tada: Now you just track completion of all the sub-lessons and tasks and record that however you need on the Assignment.