I need to implement a functionality where:
SERICE TABLE STRUCTURE
planId | scenarioId | iteration | serviceId (PK)| price
For some service, there are fee details stored in a separate Fee table,
which is structured like this:
planId | scenarioId | serviceId (FK)| rowId | sequence | feeAmount
The serviceId acts as a foreign key linking the fee details to the service. service can have multiple fee detail rows (typically 4), and users can also add new fee rows.
serviceId
If a user makes changes to the fee details of a service for one plan/scenario combination, those changes should be applied to the same service in other plan/scenario combinations.
We already have a similar functionality for services where changes to the price field can be applied across all combos . Now, we need to do similar thing for fee details. Fee details available for particular service . It can have multiple rows of fee details. users can add new rows of fee details. When a new fee row is added for one plan/scenario combo and the user clicks Apply Changes, that new row should also be added to the fee details of the same service in other plan/scenario combo, Updates existing fee rows across all combos for the same service.
price
I have challenges to differentiate each rows of fee details and how the newly added row can be appended to other service.
Discussion posts and replies are publicly visible
Add a feeReferenceId column to link matching fee rows across plan/scenario combos. When updating, find all rows with matching serviceId + feeReferenceId and update them. When adding new rows, generate a new feeReferenceId, query all combos for that serviceId, and insert the new row with the same feeReferenceId to all combos. This identifier solves the matching problem, without it, you can't reliably track which rows correspond across different combos.
Hi thanks for the suggestions. Should the fee reference id need to be unique for each rows .As I mentioned already have row id field which is start with 1 and depend on the fee details available it can be till 3 or sometimes 4 . But the only issue with it when new row added it will be defaulted to second maximum. Can the new field also the single digit incremental number for each service associate in plan/scenario combination.
Yes, feeReferenceId can be a simple incremental number (1, 2, 3...) per serviceId, not globally unique. When adding a new fee, get max(feeReferenceId) for that serviceId, add 1, and insert with this value to all plan/scenario combos. Unlike rowId which is unique per row, feeReferenceId stays the same across combos for matching fees.