sql - mysql temporal year -

i'm having trouble modeling , implementing of "temporal" events in championship db.

i have 3 entity: player, team, transfer, championship , want track players' transfer during championship ( player can transferred team during championship)

championship(id_championship*, name, year*) player (id_player*, name, number, id_team*) team (id_team, name) transfer (id_player*, id_team_from*, id_team_to*, transfer _date) 

thanks.

you can try query example:

select t1.id_player, t1.id_team_from team, t1.transfer_date periodfrom,  t2.transfer_date periodto                       (select id_player,id_team_from, id_team_to, transfer_date,          row_number() on (partition id_player order transfer_date desc) rn1)t1             inner join          (select id_player,id_team_from, id_team_to, transfer_date,          row_number() on (partition id_player order transfer_date desc) rn2)t2             on t1.rn1=t2.rn2+1             , t1.id_player=t2.id_player 

it should return players teams played in, periodfrom periodto.