Hello all:

I'm curious as to how others handle the creation and deletion of records in many to many relationships. For instance say you have two tables Invoices and Payments with a join table between them making a many to many relationship.

If your creating payment records would you go to a layout from the join table and use drop down value lists and create the record from the join table OR would you go to a layout from the payments table and create the record there and then create the join table record behind the scenes.

Deleting payment records I would think you would be better off deleting from the payment table and then delete the join record behind the scenes.

DJ

In this scenario, you certainly wouldn't want an invoice deleted if you were to delete a record in the join table. So, you'd delete the join record if either of its Parents were deleted.

And if you're on a join table record, then just delete it.

Definitely would not delete the invoice record - strictly talking about payments. So your saying if I'm on a join record - delete that and use the checkbox to automatically delete the record from the payment table?

Yes, turn on that cascade delete.

Btw, I understand that you have a many-to-many payment to invoices, but I'm wondering how typical that is. It's been a while since I built a system that applied a payment to many invoices.

Probably not that typical but I thought I'd set it up to allow for payments that are partial payments for more than 1 invoice. Maybe overkill, as I could have just as easily created separate records for each invoice to allow for partial payments.