hoirul said:
Thanks. At least now i can make the one-to-one relationships.
Two things bugging me are
1. data on say a tutor's past grades
2. the different estates the tutor is willing to go to
For No. 1, is it a one-to-many relationship that I have to use or still a
one-to-one relationship since the data is unique to the individual tutor
For No.2, say i have 30 different estates and want the form(subform?) to
include yes/no checkboxes, would I put each estate as a field in the tutor's
table or use a diferent table.
If One Tutor gives many grades, this is one-to-many. Grades would go in a
different table (which will have a foreign key tied to the primary key --
maybe "TutorID" in the Main Tutor Table). Otherwise, for every grade the
tutor gave, you would have to have a separate field in the Tutor Table. I
imagine this Grades table might also have a StudentID to indicate who is
getting the grade (and probably there is then a Student Table with a primary
key of StudentID for each student, and information about them), and also
possibly a Courses Offered Table with a primary key of CourseID, which might
also reflect as a foreign key in the Grades tables. Each of these tables
has their own unique primary key, which is used as a foreign key in other
tables to make the relationship.
It is also one-to-many relationship if one tutor visits more than one
estate -- (One Tutor, Many Estates). To test my logic in designing
tables, I often ask myself questions like "Is this estate unique to one
Tutor, AND is this tutor unique to one Estate?" A great help to me in
organizing tables was a poster who pointed out something like the following
"Do Estates have Tutors or do Tutors have Assignments to Estates?" This
would depend on the main purpose of your db -- to track Estates that have
many different qualities (including Tutors), or to track Tutors who have
many different activities (including visiting estates).
This is more complicated if you have several tutors that visit each estate.
In this case, you may need to structure your tables differently.
I manage a db for a non-profit that offers many different classes (mostly in
one place, but sometimes elsewhere). The tables are structured something
like the following -- this is simplified format for example (PK indicates
primary key, FK foreign key)
tblClass tblStudents tblCourse tblStudentRecord
ClassID(PK) StudentID(PK) CourseID AttendanceID(PK)
CourseID(FK) StName CoName ClassID (FK)
Date StAddress Teacher StudentID(FK)
Location StDetails Length
Please note that in my case, I have only one teacher teaching each specific
event, and only one event of a course on any given day.
If I had many teachers teaching the same course in different locations, I
might need a Teachers table and a Locations Table as well. Using this
structure, I can enter information about each event of the class in
tblClass, include the CourseID in that record, thus linking to all the
details in the tblCourse, without storing all this information in the
tblClass -- I only store the CourseID.
On my main Form for entering events of classes, I have a subform
(continuous) that is tied to the tblStudentRecord, so that I can enter who
attended. This subform uses both the ClassID of that event, and the
StudentID from the tblStudent. Now my tblStudentRecord does not store all
the Student info (name, address, etc) -- only their StudentID (a number). I
can use queries, forms, and reports to pull up Student info and details of
their attendance record for viewing.
I am a "newbie", but following the advice of many on this newsgroup, have
found that taking a good hard look at what information I need, and how it is
used, is critical to setting things up "right". (ps. the tbl examples
above are truncated and over-simplified from my db -- but if any MVPs see
flaws in my logic and think I may be pointing Hoirul in the wrong direction,
please point it out!)
Hope that is helpful,
Carol
