php - mysql link variable multiple data with one id -

for thesis @ school i'm making php website witch based on fantasy football site. , runned on localhost server

i'm using mysql database have problems finding out how lay database out. have players(id, name, team, position) tabel, user table(id, name, password, id player 1 in squad, id player 2, id player3, id player4) , table matches(id, date,home team, away team, result). want add scored in match. how do becaus 2 people score there can 5 people score? how do this?

all appreciated.

you want represent many-to-many relationship players_who_scored linking table. this:

create table players_who_scored (   player_id int not null,   foreign key (player_id) references players(id),   match_id int not null,   foreign key (match_id) references matches(id),   primary key (player_id,match_id) ); 

update:

if want track stats player in game, might add columns this:

create table player_stats (   goals int,   assists int,   yellow_cards int,   player_id int not null,   foreign key (player_id) references players(id),   match_id int not null,   foreign key (match_id) references matches(id),   primary key (player_id,match_id) );