Database Structure - 3 Models - Many to Many

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

@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

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..

@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

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

@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

@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.

@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

@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.

@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

@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;
        });
    }
}

@martinbean

1 Invoice can have Many Jobs right?

Also

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

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

@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 :)