Tracking history on a many to many relationship

[dcamara]

Having a small issue. We need to find a way to track the relationship history within out database.

We have 2 tables links together by a "link" table as a many to may relationship. We would like to track the history on both sides of the relationship. Anyone have any ideas on how to accomplish this.

Thanks,

-Davin

[shannah]

What do you mean by history? Can you elaborate on specifically what data you want to track?

-Steve

[dcamara]

Similar to how the history feature tracks changes to a record. I need to be able to reference any record that was links to another record.

Example:

We have 2 tables (Logical Devices and Physical Hardware)

each logical device has at least 1 physical hardware record associated it it via a M to M relationship.

When someone removes / changes that relationship (say they are replacing the hardware for that logical device) I want to be able to log that that piece of hardware was in the past associated with the logical device.

Does that make sense?

-Davin

[shannah]

You might do this by way of an afterDelete() trigger on your links table that logs the act of deleting a record.

The development version of xataface also has a beforeRemoveRelatedRecord trigger which would allow you to do this from the context of the parent table. This will be included in 1.1.6