I'm stuck - DB Design problems

[Merrie]

Hi - I have bitten off a little more than I can chew; can't quite sort out how best to organize my data for a sports result tracking DB.

• We have many employees
• Each employee has a 'team' of 5 athletes
• There are a number of sports. Each sport has a number of disciplines.
• Each athlete competes in one or more disciplines (in the same sport) and gets a ranking. Points are assigned according to ranking; specific rankings earn specific points (i.e. 1=5, 4-10 = 2)
• I want to design a form that will let me enter each athlete's ranking in each discipline as I get it (which will then generate the point score for the athlete)
• I want a report that shows (and is sorted by) total points earned by the entire team which also shows points per athlete per team.

Basically, that looks something like this(I enter the data manually here and the sorting has to be done manually, too in Excel).

TEAM Athlete1 Athlete2 Athlete3 Athlete4 Athlete5 Total
TeamA Joe Smith Sam Hughes Tom Dick Dick Harry Harry Toms
5 2 1 8

So far, I have the following tables

Athletes
AthleteCode
Athlete Name

Teams (may also need normalizing since one team has five athletes)
Employee Name
Athlete Code

Points
Result (Ranking)
Points

Event
Discipline
Event Date

Sport
Sport
Discipline

Thorniest problems are:

I'm not sure how to deal with the multiple variables; one athlete competing in several events and getting several results.

I don't know how to get the DB to assign points based on ranking; if I figured that out, I may be able to figure out how to total points per team.

And, most of all, how to produce the report.

It's probably easy - I just haven't dealt with that many relationships at once before. Can you help?

 

[Trevor G]

Draw the tables on a piece of paper then think about the linking so you should end up with a plan. You should include Primary keys in your main table's so you can link to the primary field.

Only create the initial tables and link them, then test them with your forms and queries and then reports. If all works well then start to add the other tables and build it up slowly.

You could think about it like reading a book, first you have to introduce your characters then you start the storey then you unravel the plot and eventually you get to the conclusion.

Each character can be a table.

Name your tabels like this

tblSport

Queries like this

qrySport

Forms like this

frmSport

Reports like this

rptSport

Also don't add spacing's with your field names and don't use reserved words like Date use something like SportsEventDate you can always use the properties to change the display to Sports Event Date.

Give it a go and ask questions as you go along when you get stuck.

Good luck

Trevor

 

[Merrie]

Hi, Trevor

Thanks. I have created the main tables. I'm stuck figuring out how to link them and which types of links to use - it's the relationships I can't figure out. I know all the basics of creating tables and queries and forms, etc. before, but I haven't created relationships between this many tables. In particular, I'm stuck on the relationships between athlete, event, and results and points.

 

[Trevor G]

I'm stuck on the relationships between athlete, event, and results and points

What you need to do is ensure that you tackle the event to athlete first.

The event table should have a primary key in it so that it will link to the athlete table.

I don’t think you would need the other 2 tables as the fields you want like result and points would be stored in the athlete table.
You would create a query to give you totals etc
If you want you can send me a copy of the database and I will look at it for you, but remember to zip it up first.
Trevor
emal: trevorglover@wuit.co.uk

 

[georgedwilkinson]

Let's try to keep this to the forum so we can all benefit.

Merrie, I believe what you are missing is the concept of a M:M (many to many) relationship which is normally implemented via a "middle" table called an associative or junction table. Look up those terms in help and google.

You should, with a pencil and paper, go through all the relationships between your "tables" (known as "entities" until they're actually created) and write down whether they are one to many relationships, many to many relationships, or one to one relationships (which should hardly ever happen).

Post back here if you hit a snag.

 

[Trevor G]

As you can see George is also recommending writing it down on paper and I also do agree it is best to keep it on the forum, and you can post your database here as well.

Trevor

 

[Merrie]

Hi, George

I totally agree. Where I'm stuckest is that I need a many-to-many relationship for athletes and events.

Should I be using lookups to populate the tables?

Where do I create the form where I enter the athlete's result in an event (I don't know which events the athlete competes in until each event is over - and of course there are several athletes who compete in each event).

I've got it on paper. That's not helping me to figure out which sorts of joins to create, whether or not to use lookups, and where and how to create the form, query, and report...

The relationships between athlete, sport, and team are easy enough to construct. It's event and result that I'm having trouble hooking up. The DB analyzer didn't help a heck of a lot ...

 

[georgedwilkinson]

You create a junction table between the two. I normally name the junction table after the source tables: AthleteEvents, for instance.

In this table, you will keep a foreign key (FK) reference to the athlete and an FK reference to the event the athlete is about to participate in or has participated in. You will keep other information pertinent to that pairing, including perhaps the results for that athlete (like the time, position, or whatever).

When you design the tables, I highly advise that you put an auto-number primary key (PK) in at least the "base" tables. Then after you create the junction tables, you can just open the relationship diagram tool and drag the PKs of the Athletes and Events tables over to the FKs in the junction table. You'll notice that I also make the names of my tables plural. This simplifies discussion about how the database works.

As far as how to display/populate the tables, see Pat Hartman's Many-to-many example demo database on this site. You'll essentially have 2 data entry forms, one with the event in the main form with the junction table in the sub-form and the other reversed.

Just remember, to keep a nice design, every real world category of objects should go into it's own table. That includes people, events (including the events of multiple objects getting together, like this case), places, containers, etc.

Does that help?

Give a yell if/when you get stuck again. Also, you don't have to address me specifically; there are many fine people to help here.

 

[Merrie]

Thanks much. I just addressed you, George, to say I agreed with your particular point but I'll gladly accept assistance from all who offer.

I've done quite well splitting out all objects to their own tables - it's getting them to talk to each other that stumped me. What you're saying about the one many-to-many table looks like it will fill in the gap in my knowledge. Off back to work to give it a shot. Wish me luck!

 

[georgedwilkinson]

Good luck, Merrie.

 

[Merrie]

Thanks! I tried - still stuck. I'm sure it's something glaringly obvious that I just keep missing. I feel that I'm very close...

And it doesnt' look like the forum uploader wants to upload my db. Sigh.

 

[georgedwilkinson]

Can you upload a picture of your relationships window?

 

[Merrie]

Hope so - looks like it. You'll see there's two similar tables - AthletesDisciplines and Results. Results didn't work so I tried AthletesDisciplines...

 

[georgedwilkinson]

View attachment SportsStuffDia.zip

Take a look at the attached.

You need to step back a little bit and look at this. Keep it simple. Don't re-use stuff that doesn't work (delete it).

Please notice that I set the referential integrity flag on my relationships. You should do that, though you don't need to cascade. Also, you should make your autonumber fields the Primary Key (PK) of each table. That will simplify things. You can put a Unique Key on other fields you think should be unique.

Also notice in my diagram that I have the most complex tables on the left and the simplest (with no "inheritance") tables on the right. So it really makes it clear which tables depend on what in order to work.

Also, many people will say to prefix table names with "tbl". I don't do that and I don't recommend it for tables. For other Access database objects, yes...just not tables. My reasoning is complex but it really boils down to personal taste and communications in the end. Also notice that all table names are plural: same reasoning.

What I've sent you is by no means complete. It is only a start with a very limited understanding of your system.

Please feel free to post follow-up questions.

 

[Merrie]

Thanks so much! It looks good to me. I'll have dinner, rest a tad, and then see if I can make it behave.

I greatly appreciate all the time and trouble you've put in to helping me!