Schema design

@Tray2 thanks, i'm familiar with normalization and Laravel conventions.

I'm curious where the conventions came from and if there are more advanced concepts behind them. Basically it boils down to how do I make can_use stay correct. The expired_at is actually in the subscriptions table.

For example, there might be users without subscription but are given a free trial, or partners that are given an indefinite license who also don't have a subscription or subscribers whose renewals have failed but given a grace period for now.

If I were to calculate this field in code, I can't query it. If stored as a field, I can't be sure if it is correct without further checks. Just wondering if there are other ways to deal with these real world situations.

@laracoft One way would be to have a table subscribed_users (or similar) that contains the user_id and an expire_at timestamp. If it is a user that isn't a subscriber then that user_id is not there. If it is a trial user, the expire_date should be set to the day the trial ends. For regular subscribers it shoul be set to the date the subscription ends. and when they renew the new date should be set. For life timers just leave the expire_at empty.

@Tray2 ok thanks, one other way I tried in the past was to use MySQL views, have you incorporate them into your work with Laravel before?

@laracoft Yes.

Here is an example where it's used in the controller: I've created a model for it.

class BooksIndexController extends Controller
{
    public function __invoke()
    {
        return view('books.index')
            ->with([
                'books' => BookIndexView::query()
                    ->orderBy('author_name')
                    ->orderBy('series')
                    ->orderBy('part')
                    ->orderBy('published_year')
                    ->get()
            ]);
    }
}

And the migration for the view, I would not want to attempt this query in Eloquent/Query Builder.

class CreateBookIndexViews extends Migration
{
    public function up(): void
    {

        DB::statement("CREATE OR REPLACE VIEW book_index_views AS
                    SELECT
                    (SELECT GROUP_CONCAT(a.id ORDER BY a.id SEPARATOR ',')
                    FROM authors a, author_book ab
                    WHERE a.id = ab.author_id
                    AND ab.book_id = b.id) author_id,
                    (SELECT GROUP_CONCAT(concat(a.last_name, ', ', a.first_name)
                    ORDER BY a.last_name, a.first_name SEPARATOR ' & ')
                    FROM authors a, author_book ab
                    WHERE ab.author_id = a.id
                    AND ab.book_id = b.id) author_name,
                    b.id book_id,
                    b.title,
                    b.part,
                    b.published_year,
                    CASE s.name
                        WHEN 'Standalone'
                        THEN b.published_year
                        ELSE (SELECT MIN(bi.published_year)
                              FROM books bi
                              WHERE bi.series_id = b.series_id)
                        END series_started,
                    f.name format,
                    g.name genre,
                    s.name series
                    FROM books b,
                         formats f,
                         genres g,
                         series s
                    WHERE b.genre_id = g.id
                    AND b.format_id = f.id
                    AND b.series_id = s.id
        ");
    }

    public function down(): void
    {
        DB::statement('DROP INDEX IF EXISTS book_index_views');
    }

@Tray2 wow nice, much better than what I did :)