Struggling working out DB schema

https://tray2.se/posts/database-design

Level 73

I would use neither, I'd just add a table called professionals, or some such, and add the professional user's ids to it.
Yes you should create a categories table, and you should also create a category_proffesional pivot table, and use a many to many relation.
No, keep the users table as is, and group the relevant together, like contacts, profile, and such in their own tables.

I would suggest reading these three posts.

https://tray2.se/posts/database-design-part-2

https://tray2.se/posts/properly-formed-foreign-keys-are-your-best-friends

And this one might help you write cleaner Eloquent.

https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application

martinbean

Level 80

a user is a normal user but once they apply to be a professional and are approved they will become a professional, would you guys use a simple enum for is_admin, is_professional or would you use Spatie roles and permissions?

@cushty If a user can become a professional, then like @tray2, I’d let them create a professional profile. You then implicitly know if a user is a “professional” or not based on whether they have an associated profile:

$isProfessional = $user->professionalProfile()->exists();

we have many categories that the professional can choose to cater for, would it be best to create a new table called categories? I'm struggling to work out what the association would be ie a professional has many categories maybe?

Yes, I’d also create a categories table to store categories. These can then be linked to professional profiles using a pivot table and many-to-many relation. You will then be able to look up professional profiles associated with a given category, or see which categories a professional profile is associated with.

we will have a very long application form, would it be ok to add the fields on the user's table or make a dedicated table for the application form? I am struggling with how the categories would sync with the application form

I’d make a dedicated table. When modelling things, you need to listen to yourself an how you explain things. When you use a noun (name) for something, that usually means it’s a model within your application’s domain and should probably have its own model and database table. More so if that application form then has its own state or workflow, i.e. it’s approved or rejected after submission. All of this application form-specific stuff is easier to model and representing in its own table rather than trying to shove it in your users table.

1 like

Level 4

wow thank for all the help guys,

so I will make a separate professionals table, should I have an is_approved boolean on this table? or better on the application form table? Admin will need to approve all applications and if approved they will show on the professionals' index page, if not they will not be shown.
the categories I will do it the way you guys said via a pivot and many to many, I'm a little confused about how I can use this In the application form, thinking about it I could just map through the categories table on the form and allow professionals to check ones that are applicable. Would this work?
so I'm guessing the application form will be its own table and I have to sync the categories with the application form? I am a bit confused here lol

thanks for all of your help

Level 73

@Cushty

Just add a valideted_at column to the professionals table.
The easiest would be a multiselect dropdown.
Not som confusing, Just validate and send the needed data to the correct model.

Something like this

  public function __invoke(RecordFormRequest $request, TracksService $tracksService, ForeignKeyService $foreignKeyService)
    {
        $valid = $request->validated();
        $record = Record::create(array_merge($valid, [
            'genre_id' => $foreignKeyService->getGenreId($request->genre_name, 'record'),
            'format_id' => $foreignKeyService->getFormatId($request->format_name, 'record'),
            'artist_id' => $foreignKeyService->getArtistId($request->artist),
            'country_id' => $foreignKeyService->getCountryId($request->country_name),
            'record_label_id' => $foreignKeyService->getRecordLabelId($request->record_label_name),
        ]));

        $tracksService->storeTracks([
            'track_count' => count($valid['track_positions']),
            'track_positions' => $valid['track_positions'],
            'track_titles' => $valid['track_titles'],
            'track_durations' => $valid['track_durations'],
            'track_mixes' => $valid['track_mixes'] ?? null,
            'track_artists' => $valid['track_artists'] ?? null,
            'record_id' => $record->id,
            'record_artist' => $request->artist,
        ], $foreignKeyService);

        return redirect(route('records.index'));
    }

martinbean

Level 80

so I will make a separate professionals table, should I have an is_approved boolean on this table? or better on the application form table? Admin will need to approve all applications and if approved they will show on the professionals' index page, if not they will not be shown.

@cushty If an application being approved results in a professional profile being created, then I’d model it as exactly that. You have your applications table. When a row is approved, a corresponding professionals row is created.

the categories I will do it the way you guys said via a pivot and many to many, I'm a little confused about how I can use this In the application form, thinking about it I could just map through the categories table on the form and allow professionals to check ones that are applicable. Would this work?

Yes? Just list categories in your form:

@foreach($categories as $category)
    <label>
        <input type="checkbox" name="categories[]" value="{{ $category->getKey() }}">
        <span>{{ $category->name }}</span>
    </label>
@endforeach

Then just sync the submitted category IDs when saving whatever model:

// Will be an array of the checked category IDs
$categories = $request->input('categories');

$foo->categories()->sync($categories);

so I'm guessing the application form will be its own table and I have to sync the categories with the application form? I am a bit confused here lol

Then read the Laravel docs on many-to-many relations. It has examples on how to define them, and then how to add/remove related records.

Level 4

Thx for the reply, can I ask how I could use actual real data for the categories in a Seeder? everything I have seen uses fake test data but I have a feeling categories which will have a lot of options would be better in a seeder especially when developing.

I have a list of the categories already but will I have to re-write them out in key pairs or is there an easier way to add them to a seeder? Thanks

Level 73

@Cushty Simplest is just a seeder.

This is how I do it in one of my projects.

class MediaTypeSeeder extends Seeder
{
    public function run(): void
    {
        MediaType::factory()
            ->count(4)
            ->sequence(
                ['name' => 'book'],
                ['name' => 'record'],
                ['name' => 'movie'],
                ['name' => 'game'],
            )->create();
    }
}

Level 4

@Tray2 Thanks for all of your help, would it be better to add subcategories as json or have a separate migration for them? JSON seems easier to implement

<?php

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class SubjectsTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $subjects = [
            ["name" => "Webflow", "subcategories" => null],
            ["name" => "WordPress", "subcategories" => ["Design", "Development", "Plugin Development", "SEO", "Security"]],    
        ];

        foreach ($subjects as $subject) {
            DB::table('subjects')->insert([
                'name' => $subject['name'],
                'slug' => Str::slug($subject['name']),
                'subcategories' => $subject['subcategories'] ? json_encode($subject['subcategories']) : null,
            ]);
        }
    }
}

Thanks

Level 73

@Cushty JSON does not belong in the database, and if you read the post I linked you, you should know that.

Level 4

actually, i think the best way is to do a separate table for sub categories?