Create & Delete Recs in Many to Many relationships Best Practices

[djc728]

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

[bcooney]

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.

[djc728]

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?

[bcooney]

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.

[djc728]

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.