The Core Tables: Users and Organizations

At the heart of our application are two primary tables: the Users table and the Organizations table. These tables are connected by an intermediary table called Organization Users, which not only links users to organizations but also defines each user's role within the organization. Roles include members and administrators, and these roles dictate what actions a user can perform.

User-Specific Data

We need to store additional user-specific data besides linking users to organizations to ensure a seamless experience. This includes:

• Password Reset Tokens: For users to reset their passwords.

• Email History: Keeping track of email changes in case ownership needs to be verified later.

• Remember Me Tokens: Allowing users to maintain longer sessions without frequent logins.

• Organization Invites: Handling pending invitations for users to join organizations. Users will need to accept the invite before being added, preventing any involuntary additions.

Organizations and Their Attributes

Each organization in the application has its own set of attributes such as statuses, difficulties, and access levels. These attributes are initialized with default values when a new organization is created but can be customized as needed.

Courses, Modules, and Lessons

Lastly, we'll want tables for courses, modules, and lessons. These entities are linked back to the organization, ensuring that each piece of content has specific access levels and statuses associated with the parent organization.

The Courses table links to Modules, and each Module is further linked to Lessons, creating a hierarchical structure that is easy to navigate and manage.