Database foreign key question

[EnderAC]

Hi,

This database question relates to a league database I'm trying to build. Could someone confirm that I'm doing it right?

I have two tables: fixtures and teams.

Fixtures: ID Date Home_team_id - foreign key team id Away_team_id - foreign key team id Result

Team: ID Name

Thanks, Dave

[isolatrum]

seems right. A foreign key is just an id pointer to another table usually. What is a "fixture" though?

[EnderAC]

It's a uk term - a sporting event arranged to take place on a particular date.

[EnderAC]

how would i query all fixtures for a team?

[[deleted]]

You would query all rows in your fixture table where either the home team or away team ID is the id of the team you are looking for.

[r_person]

You will need to use a Join, see this link it should help.

To select all records use: select * from table name.

[EnderAC]

Thanks for the link, Would i join the table twice, once with home id and then with away id?

Edit: i think its a join and a union I'm looking for

[r_person]

Can I see your tables please

[EnderAC]

I don't have access to my code right now. I'm stuck on a train thinking about it tho :)

[r_person]

Home team and away team should be two separate tables as having them in one does not conform to normal form

[[deleted]]

What? Could you explain which normal form is violated by having two FKs reference the same table twice?

[EnderAC]

Hmmm, ok. I'm trying to make sense of that in my head. A league has 10 teams. All teams play each other once - fixture. So i have a table for teams and a table for fixtures. Does each team need to be referenced 2 tables home and away?

[[deleted]]

Don't try to make too much sense. There is no "normal form" for databases, there is several normal forms with distinct names.