CODEWITHSUNDEEP
mysqlsqldatabase-designmysql-workbench
Wicket W. Warwick
Wicket W. Warwick

Reputation: 33

Tables for sports teams and fixtures in MySQL

I have a table in MySQL for teams. It is set up as follows;

TEAM
team_id
team_name
team_logo

I also have tables called fixtures and venues.

FIXTURES
fixtures_id
fixture_text
fixture_time
fixture_date
competition

VENUE
venue_id
venue_name
venue_location

I am building my database using an EER diagram in MySQL Workbench which automatically assigns foreign keys upon the adding of relationships between the tables. My question is a simple one but it is one I am slightly confused on. I am wondering if the relationship between the TEAM and FIXTURE table is a One to Many or a Many to Many (which would automatically create a join table). Any help is appreciated

Upvotes: 0

Views: 1607

Answers (2)

GarethD
GarethD

Reputation: 69759

It's a slightly odd one, although you could (and someone has) argue that more than one team plays in one fixture, and one team plays in many fixtures, so it is many to many, I think this is too literal, if a fixture only ever has two teams then from a database point of view it is one to many, you don't need a junction table, you can just have two foreign key columns on your fixture table, e.g. HomeTeamID, AwayTeamID, or simply Team1ID, Team2ID. It is superfluous use of a junction table unless the number of teams per fixture is variable.

Upvotes: 0

mwilczynski
mwilczynski

Reputation: 3082

Two teams play in one fixture. One team plays many fixtures. It's many to many.

Upvotes: 1

Related Questions