DESIGN DATABASE AND MANY TO MANY RALATIONSHIPS

[smita]

PLease help me with designing the tables and setting the relationships as i
am unable to work with subforms.
The databse has to be designed for interschool competions where various
types of comtetions are held. Each competion is judged by more than 3 judges.
Participants can participate in more than one competions. the average marks
by all the judges is the marks for that participant. ALSO ONE JUDGE CAN BE A
JUDGE FOR MANY COMPETIONS AND A STUDENT CAN PARTICIPATE IN MORE THAN ONE
COMPETIONS.
So the finally participantCode, JudCode, Competitioncode together has to be
unique for a marks entry. Also while entering the data competionwise
participants data is easier to add. considering all these constraints how i
create other tables than

PArticipant table
partCode
partName
.....

Competition Table
compCode
competion
no. of judges
....

 

[Allen Browne]

As well as the 2 tables you already have, you will need these:

Judge table (one record for each judge, with JudgeID) primary key
===========

CompetitionJudge table (one entry for each judge in each competition):
================
compCode - relates to an entry in the competition table
JudgeID - relates to an entry in the Judge table

Entry table (one record for each participant in each competition):
===========
compCode - relates to an entry in the competition table
partCode - relates to an entry in the participant table
entrydate - when received

EntryResult table (one record for each judging of each entry):
=================
compCode - relates to an entry in the competition table
partCode - relates to an entry in the participant table
JudgeID - relates to an entry in the Judge table
Result - Number: the value given by the judge to this entry

So, if compCode 4 is a particular competition, and Johnny Appleseed enters
(say he is participant 15), and there are 3 judges, you will have 3 rows in
the EntryResult table, like this:
compCode partCode JudgeID Result
======== ======= ====== =====
4 15 2 4.5
4 15 3 4.6
4 15 5 3.9

 

[smita]

Thanks a lot Allen. I have created the tables as u told . Just one more
question
Do i need to set "Multiple Parimary Key" for Entry result table which will
have (Compecode+partcode+judcode ) together as primary key.

 

[Allen Browne]

You can use the combination of the 3 fields as primary key if:
a) all 3 are always required (since p.k. can't be null), and
b) the combination of all 3 must be unique.

Or, you might prefer to add an AutoNumber as p.k., particularly if you have
other tables with related records from this one.