Creating database. Any suggestions? 1

[equestrian]

First, I have only ever done very simple databases. This is a much more complex job than I have ever done.

I am attempting to make a database that will keep track of information pertaining to a horseshow circuit. I have most of the tables I will need created, and I believe I have the relationships setup correctly.

tblHorse
HorseNumber pk
HorseName

tblPeople
PersonNumber pk
Address

tblShow
ShowNumber pk
ShowName

tblClass
ClassNumber pk
DivisionNum pk
ClassName

tblDivision
DivisionName
DivisionNum fk

tblEntry
EntryNum pk
HorseNum fk
OwnerNum fk (PersonNumber)
RiderNum fk (PersonNumber)
TrainerNum fk (PersonNumber)

tblEntryDetail
EntryNum pk
ClassEnteredNum fk (ClassNumber)

I feel good about the above tables. I have forms setup and they are working well. Now I need to setup tables that allow results to be entered.

For each show, each class can have placing up to 6th place. If there are more than 6 entries in a class there will only be placings through sixth.

I was thinking of settin up the ResultsTable as follows:
ResultsNum pk
ShowNumber fk
ClassNumber fk (from tblClass)
FirstPlace fk (from tblEntryDetail)
SecondPlace fk (from tblEntryDetail)
ThirdPlace fk (from tblEntryDetail)
FourthPlace fk (from tblEntryDetail)
FifthPlace fk (from tblEntryDetail)
SixthPlace fk (from tblEntryDetail)

Then I will need to create a query to fill in all but the placings. Then a form to enter the placings. Does this sound correct?

 

[equestrian]

I thought this might help:

ShowNumber ClassNumber EntryNumber Placing
2 3 12
2 3 13
2 3 15
2 3 16
2 4 16
2 4 21
2 4 15
2 4 12
2 5 9
2 5 12
2 5 13
2 5 15
2 5 16
2 5 17
2 11 11
2 11 16
3 9 10
3 10 10
3 11 10
3 12 10
4 4 22
4 8 22
4 9 22
4 10 22
4 12 22

This is the result of the query showing the entry detail.

 

[MajP]

We are on the same sheet of music, maybe a confusing choice of names on my part. I defined an event as specific class in a specific show, but maybe a clearer name is a ShowClass. So here is my renamed table and I now would get rid of the table called tblEventDetail.


tblShowClass
showClassID_pk (or use a composite of show and class)
ShowID_fk
ClassID_fk
entryID_fk
placeID_fk (from my tblPlace i.e. 1st 2nd)
other fields unique to an class within a specific show such as (start time, rink location, judge)

This is very similar to your results table, but the entity that the table is based on is a specific class in a show not on a result.

But here is the biggest issue. You have a table :

tblEntryDetail
EntryNum pk
ClassEnteredNum fk (ClassNumber)

This does not make sense because a person enters a specific class (or several specific classes) within a specific show. Your design would assign an entry to that class for every show. I do away with that table and in tblShowClass is where an entry is assigned.

 

[equestrian]

Okay, I think I understand what you are saying. Currently, I have:
tblEntry
EntryNum_pk
ShowNum_pk
AssignedNum (This is the number that the entry will wear. The same rider may wear the same number at multiple shows. Or one rider may wear a number at one show and another may wear that number at the next show. This however is the only number that the judge and rider will know.)
HorseNum_fk
RiderNum_pk
TrainerNum_pk

and

tblEntryDetail
EntryNum_fk
ClassNum_fk

and

tblResults
ResultsNum_pk
ShowNum_fk
ClassNum_fk
EntryNum_fk (from EntryDetail)
PlacingNum_fk

You are recommending I create new table
ShowClass
ShowClassNum_pk (autonumber)
EntryNum_fk (from Entry)
ShowNum_fk
ClassNum_fk

tblEntry
EntryNum_pk (autonumber)
AssignedNum
RiderNum_fk
OwnerNum_fk
TrainerNum_fk

tblDetail (allows the user to select the Show/Classes the entry is entered in)
DetailNum_pk (autonumber)
EntryNum_fk
ShowClassNum_fk

tblResults
ResultsNum_pk (autonumber)
ShowClassNum_fk

A couple of questions:

One of the things, I thought I might want to implement is making it difficult for the user to select the wrong Horseshow. I was thinking of either putting the current horseshow as the default show in the various screens. It could be a mess to clean up if some information is entered in the wrong show. I suppose I could try to do the same thing by knowing what the firt ShowClassNum_pk for a show is and using that number as the default?

Hmmmm, I think I need to get this more solidly in my mind.

 

[MajP]

After I thought about it, I did not like what I suggested. So here is the complete structure. I think this is a clean design.

tblHorse
HorseNumber pk

tblPeople
PersonNumber pk

tblShow
ShowNumber pk

tblClass
ClassNumber pk
DivisionNum fk

tblDivision
DivisionNum pk
DivisionName

tblPlacement
placementID_Pk
placeValue
strShortName
strLongName
strRibbonType
(I described this earlier holding data like, 1st First Blue ribbon)

tblEntry
EntryNum pk (autonumber)
HorseNum fk
OwnerNum fk (PersonNumber)
RiderNum fk (PersonNumber)
TrainerNum fk (PersonNumber)
(a unique combination of the above that could be in one or more classes in one or more shows)

tblShowClass
ShowClassNum_pk (autonumber)
ShowNum_fk
ClassNum_fk
(fields specific to a class in a given show)

tblShowClass_Entry
showClassNum_fk
EntryNum fk
AssignedNum
PlacingNum_fk
(this is a one to many between ShowClass and the entries in a class)


The results are in tblShowClass_Entry, but that is only one field. The entity is anything that is specific to that Entry, in that class, in that show. Their result is one such piece of information. I put assigned number here for simplification, but unfortunately for each class in a given show you would have to reenter the number. I understand that they would likely keep the number throughout the show. To fix that would require one more table.

This supports the form design that I was talking about and there is no way to assign the data to the wrong show or class.

1)Main form bound to tblShow

2)subform1 bound to tablShowClass linked to the main form by show ID. You enter all the classes for a given show or you run an append query to prepopulate.

3)subform2 bound to a query joining tblShowClassEntry-tblEntry-tblPeople-tblhorse. Linked to subform1 by showClassID. As you pick a class in subform 1 this subform allows you to add all the entries to this class and thus the show. You use a combo box to add an entry to this query and all the details will appear. The combo box will show something like
RiderName HorseName TrainerName OwnerName but will stick the entry ID into the tblShowClassEntry.EntryNum fk of the query.

After you assign the entry the other fields are on the subform for the assigned number, place (result)

 

[equestrian]

MajP Thank you for all the thought and time you have put into this. I really like the last structure. I was able to follow the logic from the first time I looked at it. I am going to start making changes.

Thanks again

 

[equestrian]

Okay I have created the following tables:

tblHorse
[tab]HorseNum_pk
[tab]HorseName

tblPeople
[tab]PeopleNum_pk
[tab]PeopleName

tblShow
[tab]ShowNum_pk
[tab]ShowName

tblPlacement
[tab]PlacementNum_pk
[tab]PlacementNumerical
[tab]PlacementName

tblClasses
[tab]ClassNum_pk
[tab]ClassNumber
[tab]ClassName

tblShowClass
[tab]ShowClassNum_pk
[tab]ShowNum_fk
[tab]ClassNum_fk

tblEntry
[tab]EntryNum_pk
[tab]HorseNum_fk
[tab]RiderNum_fk
[tab]OwnerNum_fk
[tab]TrainerNum_fk

tblShowClassEntry
[tab]ShowClassNum_fk
[tab]EntryNum_fk
[tab]AssignedNum
[tab]PlacingNum_fk

I created a form and a query to load tblShowClass.

I populated tblHorse and tblPeople.

Then I created frmEntry and frmShowClassEntrySubform.

I have combo boxes in frmEntry to select horse, rider, owner and trainer.

In the subform I have a combobox to enter ShowClassNum_fk.tblShowClassEntry that only shows the field ClassNum_fk. I currently only have one horseshow loaded into tblShowClass. When I have more than one horseshow in the table, I will need to be able to filter this so that the user can select only classes for a specified horseshow.

I would like to avoid having the user enter the assigned number for each class. MajP had mentioned in a previous post adding another table. The only way I can see to do this would be the same way we did tblShowClass. I don't think this would work well because the number needs to be assigned at the same time the entry is being put in the computer. It seems that it could get messy trying not to have duplicates in this table.

What about a text box in frmEntry?

I need to shutdown for the night, but if anyone has any ideas on whether this would work I would appreciate ideas.

 

[MajP]

take a look at this demo

http://www.4shared.com/dir/6086223/c26decf2/PartCatClass.html

1)When you add a horseshow it is assigned all classes
2)You click on a class in a horseshow and add entries in a linked subform
3) There will be no duplicates before the subforms do all the work for you.
4) Navigate the shows from the listbox

This is very primitive and the tables and queries are only representative.

 

[equestrian]

Thanks MajP. I already had all the classes assigned to the horseshow. I was trying to create a form that would allow the user to assign ShowClassNum_fk in tblShowClassEntry. I need to create it based on the entry because the user will not have a list of classes with the entries in it. Instead they will have an entry that the exhibitor has filled out where they filled in horse and rider info and circled the classes they enter.

I have the form completed. It fills in all the fields in the table giving the PlacingNum_fk a value of 7 which is the default.

I then created another form that allows the user to select a ShowClassNum_fk with a subform that lists the fields for tblShowClassEntry and allows the user to select Placing_fk.

It is working perfectly. I am now working on calculating points based on the number of entries per class. Thank you so much for your help. I am very excited that the database is almost complete.