Normalising a two-to-one relationship
Date: Sun, 31 Dec 2000 14:16:52 -0000
Message-ID: <92nf9t$d6c$1_at_uranium.btinternet.com>
I am trying to set up a database schema for a football game. Each fixture has two teams playing in it. Should that information go in the fixtures table with venue and other one-to-one information or should it have its own table?
I currently have
fixtures
fixture_id (PK)
venue_id (FK)
fixture_date
fixtures_teams
fixture_id (FK)
team_id (FK)
The problem here is that the extra table seems pointless and cannot have a primary key (unless I create a meaningless one that will be used for nothing) but having 'team1' and 'team2' as columns in fixtures seems wrong as it is not a one-to-one relationship. Maybe I'm getting hung up on terminology (don't like numbered columns or the idea of having home_team and away_team when the match may be played at a neutral ground (e.g. a cup final)).
Any help gratefully appreciated.
T
---
No wonder Van Gogh cut his ear off
Received on Sun Dec 31 2000 - 15:16:52 CET