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?

 

[TheAceMan1]

How are ya equestrian . . .

Have a look below:

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[MajP]

Looks pretty good. Do yourself a favor and take any spaces out of a name. A name with spaces requires [] in all queries and code. Use and underline or camelback. "EntryNum_pk or EntryNumPK not EntryNum pk".

The thing about normalizing your db it sometimes make form development harder, but the flexibility is always worth it. If this was me

ResultsNum_pk
ShowNumber_fk
ClassNumber_fk (from tblClass)
entry_fk (from tbl entry)
intPlaceValue (value 1 to 6)


This is normalized and I can easily show reports in place order. Your version would be very difficult to do some queries. I am also pretty sure you can have a tie in horse shows. The above version will handle that better and also the case where you have less than 6 contestants.

My place value would actually come from a table, allowing more flexibility in report writing and forms.

placeID shortName longName ribbon
1 1st First Blue
2 2nd Second Red
....

 

[MajP]

P.S.
For someone who has only done small databases you did a good job with some complicated ideas.
1) Most people would have made the mistake of making three seperate tables for owners, trainers, and riders but you did this all in the people table for use in the entry table. That is pretty good insight into an entity and will provide a lot of flexibility. I often see advance db designers with tables like "clients" and "suppliers" but nearly identical information. Or "shipping addresses and receiving addresses".

2)Your results table is a junction table that has an Entry fk, which is a key to another junction table. That is another really good insight.

 

[JoeAtWork]

Take my suggestions with a grain of salt, as I'm not familiar with horse shows, but....

I think you would add ShowNumber fk to tblEntry, as I don't see any way to link the entry to the show.

I wondering if DivisionNum fk in tblDivision should be swapped with DivisionNum pk in tblClass. Or is there even a relation between Division and Class?

As for the results, I am tempted to simplify it by not having a ResultsTable at all, but rather having a Placing field (an integer) in tblEntry, which could have values between 0 and 6 (0 means the entry did not place in the top 6).

 

[fneily]

The rules for designing a relational database are the same for a "simple" or "complex" database. They're subjective adjectives, anyway. The big boy being Normalization. Get the tables right and forms, queries, reports sort of just fall into place.
MajP's suggestion illustrates a non-intuitive concept just from the first form of normalization. In your Results table, you have the following column headings:
FirstPlace, SecondPlace, ThirdPlace, etc. Drop the First, Second, etc. and you're left with repetitive column headings - Place, Place, Place, etc. Extremely bad. Obviously, some of these fields for a single record will not be filled in. So, you'll have alot of empty fields in your table. Not acceptable. Also, in conjunction with MajP, what happens when you have less then 6 entries AND what happens if in the future it is expanded to awarding seven places? or 100? Then your table structure has to change. Not acceptable. But take a closer look: looking at where the data in a record ends, and using that has a "border" down the table, this right border will be jagged. All tables must be rectangular. From the first Normal form (rule, protocol), this creates variable length records. Taboo in relational databases. This is hard to recognize. As MajP points out, you don't need that structure.
I agree with MajP, what you presented was pretty darn good.

 

[MajP]

Similar to what Joe said. I think the real entity is an "Event" which has a result.

If I understand the model
A given horse show is something like "Spring Valley Horseshow" a class is something like "Jumping or dressage" ,and a division I am guessing is something like "Junior, Senior, Advance, Pro". If that is the case than a Show has many classes and there are many divisions within a class.

So to me an "Event" is unique entity within a horsehow defined by a Class and a division.

So depending on your buisness model may determine how you set this up.
If all shows have almost

 

[TheAceMan1]

equestrian . . .

I've been following this thread looking for more specific info on [blue]Shows[/blue], [blue]Classes[/blue], and [blue]Divisions[/blue]. Same as others here, this remains a mystery. Could you give us [blue]a more comprehensive insight[/blue] into these items?

Also, I keep getting the impression that [blue]horses and owners[/blue] belong in the same table and would lighten complexity.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[MajP]

Similar to what Joe said. I think the real entity is an "Event" which has a result.

If I understand the model
A given horse show is something like "Spring Valley Horseshow" a class is something like "Jumping or dressage" ,and a division I am guessing is something like "Junior, Senior, Pro or age groups". If that is the case than a Show has many classes and there are many divisions within a class.

Which means I think you actually have an unnatural relationship in the class table. I would not have a division foriegn key in the class table. That is because I assume the data looks something like below where divisions and classes are a many to many you are kind of suggesting a one to many.

Jumping
Pros
Seniors
Juniors
under 12yrs

Dressage
Pros
Seniors
Juniors
under 12 yrs

So to me an "Event" is unique entity within a horsehow defined by a Class and a division.

So I would slightly modify your table.
1)I would remove the division nu from my class table
2)I would make a new junction table called
tblShow_Class_Division or maybe tblEvent
I personlly do not like working with composite keys (but many people do you can go either way)

tblEvent
eventID_pk (autonumber or use the composites as a pk)
ShowID_fk
ClassID_fk
DivisionID_fk

3)I personally would rename your entry detail to tblEventDetail which is the people in each event and other specifics to an event
tblEventDetail
eventID_fk
entryID_fk
placeID_fk (from my tblResult)
other fields unique to an event (judge, rink location, start time, etc.)

Now each time I build a horseshow (creating all of the events for a given horseshow) I do not want to enter all of the events if they remain the same for each show. My kid swims and for every meet each event has specific age groups in them. If that is the case then I would have a reference table called
refTblEvents
ClassID_fk
DivisionID_fk

And it would have the default combinations of classes and divisions.

when I enter a new show name I would run an append query that would create a horse show's events by populating all of the class and division combinations from my reference table into my table events and insert the show ID for the new show. So if my reference table looked like

1 1 (representing jumping Pro)
1 2 (jumping Senior)
1 3 (jumping Junior)
2 1 (dressage Pro)
2 4 (dressage amatuer)

and my show ID is 9

it would prepopulate my tblEvent with

9 1 1
9 1 2
9 1 3
9 2 1
9 2 4

 

[MajP]

One more thing if you get this working, you may want to look into doing some work with tree views. If interested I have a lot of examples. This is a very hierarchical design which is ideal for a tree view (like windows file explored). You then can show and manage your data in a view that is collapsible and expandable looking like:

+ Spring Valley Horse Show
|- Jumping
|- Pro Division
|- Rider X
|- Rider Y
|- Rider z
|- Amateur Division
|- Rider Y
|- Rider A
|- Dressage
....

 

[TheAceMan1]

So what happen to [blue]equestrian?[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[MajP]

It's the weekend, probably out riding.

 

[TheAceMan1]

[blue]LOL![/blue] . . . I think maybe their at a horse show, [blue]sizing up the DB![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[equestrian]

Sorry, I just got back to the computer. I was out riding. Thank you all for your posts. Also, I was not totally clear on my first post. I had put pk or fk to the right of the fields to let people know which fields were primary and foreign. These letters or not in the actual field name.

I am going with the Placing table and not using the 1st through 6th fields in my Results table.

Just to clear up the division. All the classes are part of a jumping horseshow. So, the division has to do with the heights of the jumps. In general each division has 3 classes in it. However, there are some classes that are not part of a division.

So, at each horseshow the same classes will be run:
Class 1 2' Warmup - not part of a division
Class 2 2' Hunter I - part of the 2' division
Class 3 2' Hunter II - part of the 2' division
Class 4 2' Hunter on the flat - part of the 2' division

etc.

Champion and Reserve Champion ribbons will be awarded for each division. Year end points will also be awarded by division.

Now you all know more about hunter/jumper horseshows then you probably ever wanted to.

I don't want to tie Horse and Owner together because a horse could be sold.

 

[JoeAtWork]

I don't want to tie Horse and Owner together because a horse could be sold.

That's a wise decision. Another reason is that an Owner may have more than one horse (or so I would suppose), and it would make no sense to input data about the owner multiple times.

I think you have the initiative and common sense to make this work. It's nice to see a poster who actually does the background work before posting. It's becoming a rarity these days.

 

[MajP]

Also, I was not totally clear on my first post. I had put pk or fk to the right of the fields to let people know which fields were primary and foreign. These letters or not in the actual field name.

Either way is fine, but in your case I would almost keep the notation in. It is pretty common to us a pk fk identifier in the name. You have a lot of keys for a small database and it may be helpful. What I do not recommend is having the same name in two tables where one is a primary key and one is foriegn key. So I might in the event table call it "eventID_pk" and just eventID in the other tables. Your naming convention is pretty good.

It looks like your classes do not repeat within a division so I think your original table design was correct for divisions and classes.

 

[maxhugen]

I don't want to tie Horse and Owner together because a horse could be sold.

Good idea. Also (at least here in Oz) one rider may bring several horses - each one specialising in different events!

Max Hugen
Australia

 

[equestrian]

maxhugen - You are correct. There could be multiple horses owned by the same person and/or ridden by the same rider.

I am back asking for advice. I have created the Results table. I also went ahead and entered some test data directly into the tables. I also created 2 queries. One groups the data first by show, then by class, and lists the entries for each class. The other counts how many entries are in each class. This will be important when I am ready to calculate points.

I have two question. One is for MajP. I was rereading your posts and in one you wrote:

Your results table is a junction table that has an Entry fk, which is a key to another junction table.

I have my results table entrynum_fk linking to tblEntryDetail entrynum_fk. Now that field links to tblEntry entrynum_pk. Is there a problem with doing it like this? I wasn't sure if it was just a typo?

I think I am ready to create the form to enter the results. I think I need a form that has two combo boxes. One allows the user to select the Horseshow the other allows the user to select the Class. I also need a subform that allows the user to select the entry (with only entries for the previously selected horseshow and class available,) then a field that allows the user to select placing (thanks you guys for suggesting the placing table).

So, I can see what I want, but I am not totally sure how to go about this. I think that somehow I need to use the query that shows the entries for each class. Can anyone give me ideas on the cleanest way to do this?

Thanks

 

[MajP]

It is hard to tell without knowing what you decided for the results table. I will make one more pitch for this design and it will make more sense in describing the form

tblEvent
eventID_pk
ShowID_fk
ClassID_fk


tblEventDetail which is the people in each event and other specifics to an event
tblEventDetail
eventID_fk
entryID_fk
placeID_fk (from my tblPlace)
other fields unique to an event

1) I would bound the main form to table shows. You can add a new show or move to a specific show.
2) Subform 1 would be bound to the tblEvent by show ID and would be a continous form listing all classes in a show.
If every show has the same classes you can run a query to preload all classes for a given show. The following demos this

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

In this demo if you add a part you can choose to populate all categories in the junction table.
3)Sub 2 is another continous subform bound to a query joining tblEventDetail and the tblEntry joined on entryID. The entry ID is populated using a combo box showing the entries and once it is added to the query you would see the rider, trainer, owner names appear. You can link two continous subforms and there is a FAQ on how to do this or I can talk you through it.

To me this logic makes more sense
A show has many classes. Each class has many entries. An entry has a given result.

 

[equestrian]

I'm sorry I meant to include what I had put in the Results table:

tblResults
ResultsNum_pk
ShowNumber_fk from tblShow
ClassNumber_fk from tblClasses
EntryNum_fk from tblEntryDetail (shows the classes that an individual entry has entered)
PlacingNum_fk from tblPlacing


I think we may have a misunderderstanding. You are absolutely correct where you wrote:

A show has many classes. Each class has many entries.

However, all entries do not enter each class. The result then would be for a specific show, a specific class, and a specific entry.

Unless, I am missing something it seems that having a table for event and a table for show is redundant in that the show is the event. Each show has the same classes.