Relationships Headache

[Gimlett]

Hello,

I've built some satisfactory forms for data entry but I can't determine the right way to go about the relationships. As I entered runners into an event using a subform based on runners and results and a main form based on events, I found I could duplicate runners per event which renders it useless.

Fundamentally I need to enforce referential integrity (or at least ensure) to;

1. Enter only one runner per event
2. Many events at many venues but only one event per date
3. How to connect results with runner and events or ensure this can't be mixed up. Are results connected with event or member or how do I describe to both if necessary?

Sorry if I'm asking too much, I did some searching but couldn't find a suitable example.

Many Thanks

Chris

 

[Ranman256]

you need a table to store the roster of the event and what place they came in,
keyed (*) so there’s no dupes

tEventRoster:

*EventRef
*RunnerRef
Place

 

[Gimlett]

you need a table to store the roster of the event and what place they came in,
keyed (*) so there’s no dupes

tEventRoster:

*EventRef
*RunnerRef
Place

Thanks Ranman, is that like a many to many join table between events and runners ?

 

[plog]

1. What is in tblEvents.Races? How is a race different than an Event?

2. I wouldn't store each part of the time in separate fields (H, M, S). Either use a Date/Time or store everything as seconds.

3. Position probably shouldn't be stored. I bet its not independent of event, AgeCat and H/M/S--it can be calculated therefore it should be in a query and not be stored in a table.

4. What happens when a runner moves to a new AgeCat? New entry in tblRunners? It would be better to store DOB in Runners but if you don't have that I understand, but my question about moving AgeCat still holds.

 

[LarryE]

Hello,

I've built some satisfactory forms for data entry but I can't determine the right way to go about the relationships. As I entered runners into an event using a subform based on runners and results and a main form based on events, I found I could duplicate runners per event which renders it useless.

Fundamentally I need to enforce referential integrity (or at least ensure) to;

1. Enter only one runner per event
2. Many events at many venues but only one event per date
3. How to connect results with runner and events or ensure this can't be mixed up. Are results connected with event or member or how do I describe to both if necessary?

Sorry if I'm asking too much, I did some searching but couldn't find a suitable example.

Many Thanks

ChrisView attachment 105364View attachment 105365

Using some logic and reason, it seems you have:

1. Clubs that may have multiple members (Runners)
2. Each Runner may participate in multiple races at each Venue
3. Each Venue sponsors various Events or Races (Each Event has an EventName, an EventAgeCategory, an EventGenderCategory etc.)
4. Each Runner in each Club at each Event has a Result(Place, Time)

So:

1. Clubs are connected to Runners
2. Runners are connected to Venues
3. Venues are connected to Events (or Races if you prefer)
4. Events (or races) are connected to Results

 

[arnelgp]

Sorry if I'm asking too much, I did some searching but couldn't find a suitable example.

if you will search here "athletics", you will find a sample db.

 

[Gimlett]

Hi All,

Thanks for all this, I need to go away and assimilate this info and put these suggestions into practice. Sorry I didn't reply, I am having a spot of trouble when logging in, I get the message something went wrong then reset my password and still doesn't work. Then magically this morning it's working so not sure what's happening but it is a known thing or just me?

Many Thanks

 

[Gimlett]

Pat, I have tried your index suggestion and rearranged the relationships as your advice, I think this is more satisfactory but I can still add the same runner again at the same event probably because I have misunderstood or haven't applied the changes correctly. Please have a quick gander at the enclosed shots. Thank you.

Larry:

1. Correct
2. Races has gone although there are multiple races per event this is due to various categories, this is cross country so there is only one race per athlete unlike athletics meeting where you may have one runner does 100, 400 1500 etc. Although I tried to accommodate these age groups and seperate genders as well, It was getting too complicated for me but I'm not sure if this will come back to bite me when I try to include them later.
3. Yes exactly as above
4. Correct

hopefully the new relationship layout reflects your suggestions.

I don't want to introduce the time issue at the moment. I defy anyone to teach me how to work with Access time date format without splitting times into seperate fields and using total seconds for Pbs / average etc. I don't believe it's possible but what do I know? Maybe another day.

 

[Gimlett]

Thanks Pat,

I wanted to use the natural key in table events as the primary key but guess that's not possible so I use Autonumber as Primary key as you say and works fine. I created a unique index as you suggest on the natural key name and date. I'not sure if I have understood completely correctly, please could I trouble you just to check this snippet and correct as you see fit then I think that's me done for the moment and I thank you kindly and wish you a happy Christmas and to all on the forum

 

[Gimlett]

All sorted now, Thank you for the help.

 

[GregDataReno]

First principle --- avoid the use of autonumbering keys where possible - if a natural key exists, use it to save on space and indexing.

Now, since many runners compete in many events, that's a "many to many" relationship - this is perhaps a 'Scoreboard" table. We need to discover what makes the runner in an event unique. Clearly, the runner and the event in which the runner competed is unique - so we need an associative table, a joining table, to hold each instance of the runner and their event.

The runner and the event in this associative table can be uniquely identified by inheriting the primary keys from the runner table and the event table primary key. This new primary key is perfect - there is no need to confuse things by adding a (redundant) autonumbering primary key to this associative table.

Once you create this associative table and then create the relationships and then use these tables in a form, Access will honour these relationships by automatically carrying into that associative table the primary keys from the runner table and the event table; this automatic assignment of primary key values is a feature of Access often overlooked since it's not well documented, yet it's a crucial behaviour that needs to be understood by Access developers so that best-practice table design is honoured in forms.

Another principle that helps in this problem is to understand that an entity relationship model is a static representation at a point in time. This helps to recognise 'entities' - in this case we have a "runner who is or will be competing in event" fact with its own specific attributes that describe what happened to a runner in an event. This associative table is also classed as a 'weak' entity in that each instance of "runner who is or will be competing in event" relies entirely on the existence of facts in two other tables - the 'runner' and event' entites - for it to exist - no runner or no event and there's no instance of runner competing in an event

Good luck
Greg

Hello,

I've built some satisfactory forms for data entry but I can't determine the right way to go about the relationships. As I entered runners into an event using a subform based on runners and results and a main form based on events, I found I could duplicate runners per event which renders it useless.

Fundamentally I need to enforce referential integrity (or at least ensure) to;

1. Enter only one runner per event
2. Many events at many venues but only one event per date
3. How to connect results with runner and events or ensure this can't be mixed up. Are results connected with event or member or how do I describe to both if necessary?

Sorry if I'm asking too much, I did some searching but couldn't find a suitable example.

Many Thanks

ChrisView attachment 105364View attachment 105365

 

[mike60smart]

Hey

Why oh why give such wrong advice.
Every table should have an Autonumber PK
Who told you to avoid using Autonumbers?

 

[Gasman]

First principle --- avoid the use of autonumbering keys where possible - if a natural key exists, use it to save on space and indexing.

Yes, because space is still pretty tight these days?

FWIW I have always used surrogate keys.
Those 'natural' keys have a habit of changing further down the road.

 

[GregDataReno]

Hey

Why oh why give such wrong advice.
Every table should have an Autonumber PK
Who told you to avoid using Autonumbers?

mike60 - 40 years of data modeling experience has proven (to me) that autonumbered primary keys are not necessary where an existing natural key will do the job.

In nearly every instance of the excessive use of autonumbering where a natural key already exists and would do the job perfectly well, I see either users who don't understand their business, or developers who've failed to elicit from users their true business case, or both - liberal use of autonumber primary keys is not a substitute for a well-understood business need.

So, why my assertion about natural keys in place of autonumbering primary keys...?

1. Natural keys are self documenting which greatly simplifies the design of the front end and especially the creation of reports.
2. Natural keys are familiar to users and this simplifies communication amongst users and amongst users and developers when exploring the business need.
3. Natural keys remove the need to (always remember to) create a unique index to ensure relational integrity exists in stored data.

Here's my example model to support my argument:

If a particular 'race' is called, for example, "Iditarod Trail Sled Dog Race" then that name will serve perfectly well as the primary key - there is only one such race.

Race: RaceName (PK), <other non-key attributes>

Now we need a table to hold the occasions when this race is run - ie the "Race Event"; this 'event' table inherits the Race primary key to which I'd add the date when the race us run to make a composite primary key - each running of the race is now unique:

RaceEvent: RaceName (PK, FK), DateHeld (PK), <other non-key attributes>

We could then create a table that shows all entrants in an instance of the race:

Competition: RaceName (PK, FK), DateHeld (PK, FK), TeamID (PK, FK), <non key attributes, like PlaceAchieved, TimeStarted, TimeEnded etc.

...no autonumber primary key required yet.

However, there are occasions when autonumber keys are necessary -- when no natural key exists. For instance, consider a database storing riverwater quality -- sites along a transect where samples are taken may not exhibit any special characteristic unique to each site - they're just sites 1, 2, 3 etc. We don't need to know anything specific to each site, but we do want to be able to assign the correct set of results to each site so we don't end up with duplicates, or missing data etc. I'd probably use autonumber primary key for each site in this scenario to ensure referential integrity and functional dependence between all site-related ata. (I note - from experience - that a site's spatial coordinates - latitude/longitude for example - are not suitable as primary keys because they're not reliably discreet numbers and not immutable).

--

I'm very interested in any evidence you have to support your claim that "Every table should have an Autonumber PK".

Cheers
Greg

 

[GregDataReno]

I'm guessing that you don't use Access FE's much. A single field natural key won't be a problem but a multi-field natural key will prevent you from selecting rows using a combo or listbox which is why I always recommend autonumbers for PKs with Access. The combo and listbox require a SINGLE unique identifier for selection purposes. They are not capable of creating a multi-field FK.

Pat - this is true; so don't use complex primary keys in lookup tables; I don't. But if it's unavoidable, then I'd code the solution in a modal popup form and post the PK values in to the receiving form (table). Alternatively, I could use a combo/list box for each key field but that's a messy and inelegant solution.

 

[ebs17]

If a particular 'race' is called, for example, "Iditarod Trail Sled Dog Race" then that name will serve perfectly well as the primary key - there is only one such race.

Your primary key occupies 29*2+10 = 68 bytes. A long value requires 4 bytes.

A key is used for comparative operations such as linking, filtering, grouping, sorting. When you think of mass data processing, there is a very big difference in terms of effort and performance whether you have to match 68 bytes or 4 bytes with your peers.

 

[GregDataReno]

Your primary key occupies 29*2+10 = 68 bytes. A long value requires 4 bytes.

A key is used for comparative operations such as linking, filtering, grouping, sorting. When you think of mass data processing, there is a very big difference in terms of effort and performance whether you have to match 68 bytes or 4 bytes with your peers.

Good point. So let's use a unique surrogate key initialism like "ITSDR" - that would (probably) still satisfy my criteria. It'd require a little extra leg work to present users with the full race name in selections and on forms and reports, but it's almost self-documenting as users become familiar, and far better in my view than something like "Race=17" for example. But putting aside the space issue, surrogate keys that require educating users are probably better replaced with the actual value like "Iditarod Trail Sled Dog Race" - no user education required, no mental gymnastics and no extra steps when building queries.

 

[ebs17]

I don't know how you program. Users get a graphical interface, they don't look at tables. This way they are never confronted with surrogate keys and never have to think about it. Natural keys don't change? I live in the east of good old Germany, our history goes back further. A lot is changing.

 

[GregDataReno]

I don't know how you program. Users get a graphical interface, they don't look at tables. This way they are never confronted with surrogate keys and never have to think about it. Natural keys don't change? I live in the east of good old Germany, our history goes back further. A lot is changing.

Good point - which is why (where it has an impact) I'd never use a mutable concept like Country Name as a primary key... not only do the names change/merge/diverge/subsume; so do the boundaries (definitely calls for a spatial database solution).