Primary key as multiple foreign keys in single table

[Saracen]

Hi,

I've trawled the web and found lots of answers relating to sql dbs etc and a couple relating to access but none seem specific enough to answer my queries.

I'm developing a simple sporting records db. I have a 'Competitor' table listing competitors as follows: CompetitorPK, Name, Division, Club. I have a 'Contest' table listing contests as follows: ContestPK, Competitor1_FK, Competitor2_FK, Winner_FK, Score etc. My question is have I modelled tables correctly i.e. CompetitorPK will appear in three columns of Contest table. How do I define this relationship? What alternative is there to what I have done.

I intend to use forms to populate both tables (independantly obviously). This might seem quite simple but your answers will guide me later on in terms of querying etc.

Thanks for any help.

 

[Galaxiom]

Since Competitor fields repeat they should be implemented via a junction table.

The Contest table would hold the common information about the contests' location, date etc.

The CompetitorContest junction table has a separate record for each competitor with fields for the FK of the Contest, CompetitorFK and Score. There is no field stored for the winner as this is already determined by the scores.

This will make it far easier to query the scores for any competitors without having to union Competitor1Score and Competitor2Score fields that would be required in your proposed structure. A single score field that held the scores of both competitors would be a breach of database design principles.

Also note how this structure allows for any number of competitors in a single contest.

 

[Saracen]

Okay,that's good, a join table. However how do i populate the contest table (with common data) and the competitor/contest join table from the same form. Also how to create the multiple records in the said join table from that single form for each contest? Thanks.

 

[Galaxiom]

The junction table is populated via a subform on the Contest form.

The Link Fields automatically enter the Contest key into the junction table.

The CompetitorFK would typically be added from a combo based on the Competitor table. You can add niceties such as removing the first competitor from the combo list so a competitor cannot contest themselves.

BTW. The subform can be made to look like its controls are part of the main form by removing the borders, selectors and other extraneous features of the subformcontrol and its sourceobject

 

[Saracen]

Thanks Galaxiom, exactly what i was trying just before your reply. Thanks.

Also I had originally tried to just populate contest table with single form using unbound "detail" combos (e.g county, city, club etc.) to filter 2x bound compeititor combos including, as you say, delisting competitor from competitor selection combo once selected. This worked really well but i couldn't quite figure how to relate this contest table to competitor table given competitor pk occurring as fk multiple times in each contest record. Join table solves this nicely as does form/subform structures.

Thanks alot!!!