Database Structure - 3 Models - Many to Many

Published 7 months ago by zaster

3 Models are there

  • Job
  • Invoice
  • Payment

Relationships between these are as below

Job and Invoice - Many to Many

jobs table

invoices table

invoice_job table

Invoice and Payment - Many to Many

invoices table

payments table

invoice_payment table

I need to access payment records through jobs Job and Payment will have a many to many relationship based on the other 2 models(Invoice and Payment)

              @foreach ($jobs as $job)
                @foreach ($job->payments as $payment)
                  <tr>
                    <td><a href="{{ route('job.edit',$job->id) }}">{{ $payment->pivot->job_id }}</a></td>
                    <td>{{ $payment->payment_no }}</td>
                    <td>{{ $payment->payment_date }}</td>
                    <td>{{ $payment->payment_amount }}</td>
                    <td>{{ $job->job_status }}</td>
                  </tr>
                @endforeach
              @endforeach 

What kind of table structure should i have?

Should i maintain 3 tables for Jobs and Payments?

Can i use HasThrough for many to many?

shez1983

looks like you have TWO distinct many to many.. or are you looking to relate job/payments?

zaster

@shez1983

job and pyaments will be indirectly related. What i need to understand is whether i need to maintain direct relationship between the two

I need to access payments based on job_id

as mentioned in above code

shez1983

i think this part is wrong

invoices table

payments table

invoice_payment table

can a person pay for two invoices at the same time?

in situations like these you will have to promote one of the belongsTOmany to a Model instead of using belongsToMany ie invoice_job should be an entity and then you can create a relationship with it and payments.

has many through doesnt work in all cases.. its quite finnicky.

another solution would be to create a view in SQL and then use that view as entity etc..

zaster

@shez1983

can a person pay for two invoices at the same time?

1 payment can have many invoices - A payment can be made for multiple invoices( I am using dynamic forms to achieve that, and this is a data entry part - not a real person paying for two invoices) 1 invoice can have many payments - Because partial payment is possible

How can i make invoice_job as an entity? also Need to check on creating a view in SQL also, do you have anything that i could refer to

martinbean

@zaster Why is invoices and jobs a many-to-many relationship? Why would a job need to be invoiced for more than once?

zaster

@martinbean

In January we are we opening a job for 1000 Nos of items (Job Number - 2280) but nothing is delivered because we need time to prepare the items. We need to open a job like this because we have payments related to that job which we need to track.

in February, customer requests for 250 Nos of Items (Invoice Number - 760)

JN 2280 - IN 760

in March, requests for 500 Nos

JN 2280 - IN 820

in April, requests for 250 Nos (Job Completed)

JN 2280 - IN 871

at this point we have received payments for IN 760 and IN 820

I hope this makes sense

martinbean

@zaster The way I see it is, you have jobs which can be invoiced in “phases”. Therefore, it would be the actual invoice line items that belong to a job. A job would have many invoices through an InvoiceLineItem model.

zaster

@martinbean so a Job model might have many Invoice models through an intermediate InvoiceLineItem model. In other words a Has Many Through relationship, like mentioned below. Is that what you mean?

jobs
    id - integer


InvoiceLineItems
    id - integer
    job_id - integer


Invoices
    id - integer
    invoicelineitem_id - integer

martinbean

@zaster Almost. The invoice_line_items table would have an invoice_id rather than invoices having a invoice_line_item_id column, though.

jobs:

  • id

invoices:

  • id

invoice_line_items:

  • invoice_id
  • job_id

An Invoice model has many InvoiceLineItem models. An InvoiceLineItem model belongs to both an Invoice and a Job.

zaster

@martinbean

How should i get the total of the invoice_line_items of a job ? I suppose i should store it in invoices table

How to do that

Currently my invoice_line_items table has the below mentioned columns

invoice_id
job_id
invoice_line_item_date
invoice_line_item_amount
martinbean

@zaster I’m not sure why you have a date column in your items table; the date would come from the invoice itself. Also, you don’t really need to the invoice_line_item_ prefix.

You can add an accessor to your Invoice model that calculates the total from its line items:

class Invoice extends Model
{
    public function items()
    {
        return $this->hasMany(InvoiceLineItem::class);
    }

    public function getTotalAttribute()
    {
        return $this->items->sum(function ($item) {
            return $item->amount * $item->quantity;
        });
    }
}
zaster

@martinbean

1 Invoice can have Many Jobs right?

Also

invoice_line_item can be taken as a real invoice to the customer right?

martinbean

@zaster An invoice would be related to jobs through its line items.

zaster

@martinbean

I have given up on this , did a workaround but i know that's not the right way to do it. I need to develop my knowledge on this area and will be back to work on this in the right way.

Thank you very much for all your guidance and support :)

Please sign in or create an account to participate in this conversation.