Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-26-2010, 02:23 AM   #1
flebber
Newly Registered User
 
Join Date: Mar 2010
Posts: 19
Thanks: 1
Thanked 0 Times in 0 Posts
flebber is on a distinguished road
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.

flebber is offline   Reply With Quote
Old 11-26-2010, 02:24 AM   #2
flebber
Newly Registered User
 
Join Date: Mar 2010
Posts: 19
Thanks: 1
Thanked 0 Times in 0 Posts
flebber is on a distinguished road
Re: Overdone Normalization

Should I combine the two comment tables or keep them seperate?
flebber is offline   Reply With Quote
Old 11-26-2010, 02:25 AM   #3
flebber
Newly Registered User
 
Join Date: Mar 2010
Posts: 19
Thanks: 1
Thanked 0 Times in 0 Posts
flebber is on a distinguished road
Re: Overdone Normalization


flebber is offline   Reply With Quote
Old 11-26-2010, 04:37 AM   #4
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,305
Thanks: 51
Thanked 918 Times in 889 Posts
gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light
Re: Overdone Normalization

Quote:
Originally Posted by flebber View Post
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

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.

Last edited by gemma-the-husky; 11-26-2010 at 04:48 AM.
gemma-the-husky is offline   Reply With Quote
Old 11-26-2010, 04:52 AM   #5
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,305
Thanks: 51
Thanked 918 Times in 889 Posts
gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light gemma-the-husky is a glorious beacon of light
Re: Overdone Normalization

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 11-26-2010, 09:31 PM   #6
flebber
Newly Registered User
 
Join Date: Mar 2010
Posts: 19
Thanks: 1
Thanked 0 Times in 0 Posts
flebber is on a distinguished road
Re: Overdone Normalization

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.

flebber is offline   Reply With Quote
Reply

Tags
sports , tables , unique

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Normalization? What does this mean? Rick Stanich General 14 11-10-2009 04:42 AM
Normalization to 3NF hasse76 Theory and practice of database design 2 10-13-2009 01:08 AM
Help with normalization ALui Tables 5 08-12-2009 09:48 AM
Normalization....help me please!!!! tower2108 Tables 2 04-07-2009 02:09 AM
Please Help with Normalization! JimboUK Tables 3 07-28-2003 05:04 AM




All times are GMT -8. The time now is 10:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World