Geek Answers Handbook

How do you normalize one-to-one or other relationships?

I store data in baseball statistics and would like to do this using three tables: players, battingStats and pitching. For the purpose of the question, each player will have batting or pitching statistics, but not both.

How would I normalize such a relationship in 3NF?

+5
source share
3 answers

PlayerId will be the foreign key in the BattingStats and PitchingStats tables

[and do not forget to specify temporary measurements (season, year, etc.) in the statistics tables]

and, by the way, this is a bad assumption: as far as I know, jugs can fight too!

+6
source

. Normalization .

, ( 3NF):

Players:        ([player_id], name, date_of_birth, ...)
Batters:        ([batter_id], player_id)
Pitchers:       ([pitcher_id], player_id)
Batting_Stats:  ([batter_id, time_dimension], stat_1, stat_2, ...)
Pitching_Stats: ([pitcher_id, time_dimension], stat_1, stat_2, ...)

[] , , . Batting_Stats Pitching_Stats Batters and Pitching .

, , .

UPDATE:

, , , , :

Players:        ([player_id], name, date_of_birth, ...)
Roles:          ([role_id, role_type], player_id)
Batting_Stats:  ([role_id, role_type, time_dimension], stat_1, stat_2, ...)
Pitching_Stats: ([role_id, role_type, time_dimension], stat_1, stat_2, ...)

role_type . Batting_Stats Pitching_Stats (role_id, role_type). player_id , . , Batting_Stats.role_type = 'Batter' Pitching_Stats.role_type = 'Pitcher'. , Batting_Stats , . Pitching_Stats.

+2

, ( UNIONed ID , ).

, , , FK .

But any of them are probably closer to metal than you want.

+1
source

More articles:


All Articles