I am working a scenario where I have a parent item called "TEAM", with a 1 to many of "COACHES" AND "PLAYERS". The team, coach, and players each have a parentid column in their table that referencing a parent item in that same table. These teams can join events which have a certain end date, or can be archived. Once a team joins an event, I create a child copy of the parent since that team may change in the future, but should stay the same once the event has past. I am using SQL Server and thinking the best way to do this is triggers. Once something is updated, added, or deleted from the parent propagate it to the child. If the event has passed or has been archived then don't update, add, or delete anything in the child. Is there a better way to do this?
Asked
Active
Viewed 42 times
2 Answers
0
This sounds similar to making a table auditable. Not sure if this is better, but you might make the team read only, and whenever the team changes create a new team record, with each team record having startdatetime and enddatetime, with current teams having null enddatetime. That way events will always have a reference to the team as it was when the team joined the event.
BlackICE
- 8,816
- 3
- 53
- 91
-
Very interesting, just worried about the overhead. There are multiple tables involved like team address, team address tate, team coaches, teach players, team coach address, team player address etc. – Mike Flynn Sep 24 '13 at 05:10
-
yes, auditing (which is basically what you're doing) gets more fun the more tables that are involved. – BlackICE Sep 24 '13 at 05:11
0
I just went with a parent/child relationship of the same table. Any updates to the parent will cascade to the child via code unless a certain date has passed.
Mike Flynn
- 22,342
- 54
- 182
- 341