Overdone Normalization

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.

Should I combine the two comment tables or keep them seperate?

flebber said:

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.

Click to expand...

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

or
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.

wrt comments

it depends whether you think of the comments tables as being all just comments - and carry a flag of some sort to distinguish between the type of comment

or whether you think of them as separate.

some of this comes down to your development thoughts. the first way, you have to code your queries and forms to deal with different types of comments, but this gives probably you flexibility to automatically add in other sorts of comments

having separate tables may be easier, but then you have to duplicate the queries and functionality.

if the tables are completely normalised, there should be little differenece between the storage requirements.

Thanks for the reply, I will have a little bit of a think about the way to go. My day has been inconveniently interrupted by work so I will have a peep a little later tonight.