specific normalisation question

[stevehooley]

Hi everyone,

I'm trying to get to grips with normalisation as it applies to a specific example database I'm working on (that I will ultimately be using to teach other people ). If anyone could help with the following issue I would be very grateful:

I have a table of football (or soccer) teams (TEAMS) with the fields

team_id
team_name

I also have a table of results (RESULTS) including the fields

result_id
result_hometeam
result_awayteam
result_homescore
result_awayscore
etc.....

The result_hometeam and result_awayteam fields both contain team_id as links to the TEAMS table.

Does the fact the team_id appears in two columns in the RESULTS table break any normalisation rules?

Should I have it split further so each row shows one team and their home/away status is included as another field?

I've confused myself with this stuff.

 

[KenHigg]

Looks fine to me. I might have considered the result table more of a 'match' or 'game' table as the 'result' would seem to be more of an attribute of a match (or game).

 

[stevehooley]

Hi Ken,

Thanks for the prompt reply.

Seeing those team_ids repeating in the columns just caused me to question it for a moment. I'll plow on...

 

[KenHigg]

The only other thing I can see is that you may want to do some logic to prevent the same team from being in both fields...