Originally Posted by flebber
I wonder if I am overdoing the normalization and there might be a smoother better way to form my data. I was hoping a picture would explain it better. Essentially per competitor there could be multiple comments of multiple events but only singular comments per event for each competitor.
I hope this explains it really looking to some feedback on what I can do better.
Ok - are these horse races. i would probably have a separate meeting and race table, compared with your single event table - otherwise you will be duplicating some details about the meetings. even here there are niceties to think about - eg - is it better to store the going in the meeting table or the race table.
then you have your horse table
now the runners are given in a race/horse junction table
finally - comments. so if there is only 1 comment per horse per race, the comment can go in the horse/race junction table - since this uniquely defines the entity. if you need multiple comments (eg, one for betting / one for race performance, etc) then you could either have two fields in the horse/race table, or a linked table. if you definitely need a varying number of comments, then you must have a sub-table
meeting = cheltenham 3rd December, meet id (autonumber) 2435 say
race = race 2.30, at meet id 2435, race id (autonumber) 7064 say
horse = denman, id (autonumber) 2213 say
horse-race = raceid, horseid, position, sp, distance away, time etc etc, odds, comments, race comments etc etc
horse-race-comments, linked to horse-race, if you have lots
does that make sense?
I would also definitely add autonumber keys to link all these tables together.
If you need to add jockeys/trainers etc, the same things apply.
You can't necessarily have too many tables - theoretically you need the right number to describe your data without redundancy. you might decide to de-normalise and not have certain tables. eg betting odds - theoretically you want to track every change. in practice you might decide to store the odds in the horse-race table, and just show "opening odds" and the last 3 odds for each runner. depends how much detail you need.