Many To Many Tables

Morning All

I was wondering if i could pick your brains on how best to lay out a section of a site i'm working on.

Essentially, there are users and admins to which users can submit a form to order software.

Users can login, purchase software files (t_files table) however there are around 11 options they can select in relation to the software. . (Im guessing these would be best on their own table?) From this i also would like to generate an invoice at the time when the user submits the file request.

I was just wondering how best to lay this out in regards to foreign keys etc.

Also later down the line for reporting admin would like to pull the above stats etc based on the user. i.e how many files they have remapped, invoices against that user etc etc.

From the above, i'm guessing i would have 3 tables:-

Schema::create('t_files', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->integer('dealer_id'); // This would be filled by the session id of the user unless there's a better way?
    $table->string('');
    $table->string('');
    $table->string('');
    $table->string('');
    $table->string('');
        $table->timestamps();

Schema::create('t_files_options', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->integer('t_files_id');
    $table->boolean->('')->isnullable();
    $table->boolean->('')->isnullable();
    $table->boolean->('')->isnullable();
    $table->boolean->('')->isnullable();
    $table->boolean->('')->isnullable();
    $table->boolean->('')->isnullable();
    $table->boolean->('')->isnullable();
    $table->boolean->('')->isnullable();
        $table->foreign('t_files_id')->references('id')->on('t_files')->onDelete('cascade');

Schema::create('t_files_invoices', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->integer('t_files_id');
        $table->foreign('t_files_id')->references('id')->on('t_files')->onDelete('cascade');

Thank you in advance all.

ahmeddabak

6 years ago

Best Answer

Level 47

This would work for now, but what if two years from now you wanted to sell something else on the platform, say support services for the software, this wont work any more.

create an orders table, it should have many order-able items which is a morph relation to a file or a service.

now you do not need an invoices table. because an invoice is an order printed on paper, there you can save all the data you want.

i do not know about the file options, but i would store them as a json field on the items table if possible.

1 like

ahmeddabak

6 years ago

Level 47

2 likes

ollie_123

6 years ago

Level 6

Hey @ahmeddabak thanks for your advice. I hadn't thought of it like that.

Thats a great idea. Scalable too.

ollie_123

6 years ago

Level 6

Hey @ahmeddabak just coming back to this, just prepping my migrations & models. After reading Laravels docs, i'm a little unclear on the one to many / many to many. Essentially, i have a form that the dealer fills in. The form has input fields and a selection of options with each option costing a different amount. Essentially, i've drafted the models up as follows:-

// App\User

protected $fillable = [ 'usual fields.....' ];

 public function invoice()
    {
        return $this->hasMany(App\Invoice);
    }
___________________________

// App\Invoice


protected $fillable = [
        'user_id', 'order_id', 'paid_at', 'payment_method', 'amount'
    ];

public function order()
    {
        return $this->hasMany(App\Order);
    }

___________________________

// App\Order

protected $fillable = [
        'user_id', 'make', 'mdl', 'mdl_year', 'engine', 'transmission', 'fuel_type', 'reg_no', 'stage', 'product_id'
    ];

    public function invoice()
    {
        return $this->belongsTo('App\Invoice');
    }

___________________________

// App\Products

protected $fillable = [
        'product_name', 'product_cost', 'product_sale', 'status'
    ];

My plan is to do a product foreach loop on the order page for users to select multiple products at different values.

The bit where i'm confused is the inverse relationship, on the App\Order should i not have $this->hasMany('App\Product);?

Also when i insert into the Order page how does it insert into the invoice table etc etc.

Huge thank you in advance. Just struggling to get my head round this lol.

ahmeddabak

6 years ago

Level 47

To fully understand your data structure what are those fields

'make', 'mdl', 'mdl_year', 'engine', 'transmission', 'fuel_type', 'reg_no', 'stage' are those the options, or what exactly.

ollie_123

6 years ago

Level 6

@ahmeddabak - Essentially they are the input form fields that the user selects regarding their vehicle. This form also draws optional chargeable service options from the products table that i was planning to put a foreach loop for on the form.

Please or to participate in this conversation.