database design

[Rafty]

Just a really quick question.....I would like to know the easiest design for the following criteria.

I have over 150 dogs being showen over two days, I have to enter each dog breed name for each day. As the judge on one day differs from the judge on another.

At the moment I have created a relationship between the dog's id number on one table and the judges on another.

As I want to create a drop-down list for the dogs name's then show the judges as a result. Is this the best way to do it????

An example how I would like the end result to be is at:

http://www.springfair.com.au/2001/schedule.asp

thanks heaps
bec

 

[perrymans]

I think I am missing the point of making this a database. There are only two days and one day has one judge and the other day has a seperate judge. Sounds like a list is what you want. Unless you are planning on doing this for several events. In which case maybe have the following:

tblEvent
-EventID
-EventName

tblJudges
-JudgeID
-JudgeName
-EventID (This should probably be in a seperate table like the tblAttended_Dogs. Make a tblAttended_Judges since I assume they can work more than one event)

tblBreeds
-EventID (I see a need for this to allow for multiple event tracking, unless the same judge judges the same breed at every event)
-BreedID
-BreedName
-JudgeID (to show who will judge this breed)

tblDogs
-BreedID
-DogID
-DogName
-Owner
-Handler
-Address
-City
-State
-Zip
-Phone
-Email

tblAttended_Dogs
-EventID
-DogID
I think this should be here to track which dogs attend which events. This method is better than putting the EventID into the tblDogs which will end up giving you columns such as Event1, Event2, Event3; which violates Data Normalization.

Let me know what else I can help with.

Sean.

Trivia: Name the band you said "Happiness is a warm gun"

 

[Rafty]

Dear Sean,

Thanks heaps for the lists, Just to verify in my dumb head I need to have relationships b/t all the tables.

thanks heaps
bec

 

[perrymans]

Relationships should be the ID fields between tables. EventID to EventID. DogID to DogID. Make the elationship between each of the ID fields. Remeber that it matters which side you start out with (dragging from tblEvent to tblAttend_Dogs, rather than tblAttended_Dogs to tblEvent). The master should be dragging to the slave. DogID originated in tblDogs, so for the DogID relationships, always drag FROM tblDogs.

I didn't say anyhting about the relationship mentioned in your original post, but it didn't make sense to me. having a DogID and JudgeID linked, I thought didn't work. I could be mistaken. But if DogID 3 = Sheltie and JudgeID 3 = Tom Sides, then you have a missrepresentation of the data. Maybe someone can confirm that I am correct in my thinking. I am 99% sure that I am correct though. Theoretically you could get away with it if you had one judge per breed, but it still isn't the correct way to set it up, not to mention the pain of matching your JudgeID numbers to the breed numbers. Better to have the JudgeID column added to tblBreeds.

Good Luck> Sean.

P.S. You'll get more response on future posts by submitting to the Microsoft Access: Other Topics group of this site.