Need Designing Help

[Confused]

I'm trying to create a data base where the Record will consist of a form to
input data for a dance title, Included in that form I need to insert the
dance studio that will be performing that dance (that has a drop down menu to
pick from a list of dance studio) and then i need to add the names of
multiple students from a list from that studio only that will be in that
dance only. So i need to be able to chose from a list of students from that
dance studio. And i need to be able to chose multiple students for that
dance....i know i have to use sub-forms but i having a difficult time
designing this.

 

[KARL DEWEY]

Studio --
StudioID - autonumber - primary key
Name -
Addr
City
State
ZIP

Student --
StudentID - autonumber - primary key
LName
FName
Phone

DanceSession --
SessionID - autonumber - primary key
StudioID - number - long integer - foreign key
Type - Tango, Waltz, etc.
Day - Mon, Tues, Wed,

StudentSession --
StudentID - number - long integer - foreign key
SessionID - number - long integer - foreign key
Paid - Yes/No

Set one-to-many relationship from primary key to foreign keys.

 

[Klatuu]

Couple of observations, KARL.
You don't show any relation between student and a studio. As it is not
impossible a student could attend more that one studio, perhaps a junction
table to represent that relationship.

Also in the Dance Session table you have Type. There should be a Type table
and the Type field in the Dance Session table should be a foreign key to the
type table.

 

[KARL DEWEY]

I have the student related to Session and then to studio.

 

[Klatuu]

I think that is incorrect. A student may be a student at a studio without
being assigned to a session. I would think that when constructing a
session, one might be looking for students from a specific studion.

But, we don't really know the business rules on this, so it could be either
way.

 

[Evi]

The user said that a session is set up with students chosen from only one
studio.
What if we add to Karl's design (as I think he intended)

TblStudioStudent
StuStuID (primary key)
StudentID
StudioID


But I'm not sure about the next bit. Instead of having StuStuID as the
foreign key in the StudentSessionTable, how about going with Karl's design
and using this table only to filter the combo which allows the user to only
add students from that studio which appears in TblStudioSession so that
StudentID becomes the foreign key in TblStudentSession

Or would the student's name be enough to identify them?
If we need the Student AND the studio to identify the student, then we would
need to concatenate StudentAnd Studio in our combo and use StuStuID as the
foreign key in the StudentSession table.

Evi

 

[Klatuu]

The user said that a session is set up with students chosen from only one
studio

Then there does need to be some way to relate a student to a studio.
If the student can belong to one and only one studio, an foreign key the the
studio table will suffice; however, if a student can be associated with more
than one studio, the junction table I recommened previously will be
necessary.
'