Hi, I am have trouble when saving associations for a link tables. Perhaps my design is unnecessarily complex, but here goes.
I have 4 tables
teams
members
officialtypes
seasons
all linked with a table
team_officials
which contains
id, members_id, teams_id, officialtype_id, season_id
All these fields in one record represent a unique and valid entry, and there can be many combinations of all the ids in the table as long as only one combination of all 4 exist. eg
team_id member_id officialtype_id season_id
1 100 2 4
1 101 3 4
1 89 2 3
etc
All the fields must also exist in one of the other tables. ie member_id must exist in members etc.
I have gone with this design because it is possible that a team can have an unlimited number of officialtypes which are user defined and not code defined. Theoretically a member can also have multiple officialtypes in the same team.
I currently have the associations set up with respect to the Teams model and I have been able to build an array of data and pass it to newEntity and save the data. It all saves correctly, however my problem starts when I run an update then the associated record in team_officials is not detected as unique and another duplicate record is saved instead of it being updated.
I am also hoping that with the correct associations I can get all the related official_types (and hence member_ids) back in a query for a specific team.
Therefore, I am looking for the correct way to define all this.
I haven’t posted code because firstly I am after some advice on if I am going about this the best way and I therefore have something wrong in a model (Table) somewhere OR if the design should be better and therefore an easier set of associations.
If you suggest a better design would you please describe the associations I would require to make this work properly when saved from the team model.
Thanks in Advance,
Jason.