I am modifying a relational database and in the E/R model, I find that I have the table users contract (relationship) bonds.
But here comes the problem, I need to know what bonus each user contracts and a bonus is applied to an action (that is, to a contract) this is where I have my problem.
Could I get a contract-to-acting relationship?? the bonus table is only used to store bonus types.
the relationship between users and bonuses is M:N but if a user hires a new bonus, I have to know what bonus he had previously, what bonus he wants now, the remaining time of the previous bonus and the actions on that previous bonus... But if the user contracts a new voucher, the remaining time is added with the time he had and the type of voucher he had is updated for the one he wants now, whether it is the same or not
I had thought of adding contract to the relationship, since a new table appears there, the attributes user, dateContractFirstBono, the bonus, TimeRemaining, if it is active, if you request a new one, then which one, the time you request to update the previous bonus and How should I know the previous bonus, another column BonoPrevious and DateBonoPrevious. All this would go in the contract relationship (That's what I had thought)
Now it would be related through applies with action (an action is applied to a contract) and it would be related to a 1:N cardinality and in action record the contract code
I'm not asking you to solve it for me, it's just knowing how I could do it better, because I need to generate a history
Thanks for the help and I hope you understand.
All the best
It has been resolved, as the user @ernestojimenez recommended to me. A couple more tables, one history and one contract. Also add the contract code and bonus code columns to the users table.
It has been a good solution, easy to implement both in the model and in code.
Thank you