Relationships between Tables

jsy165

文章标题：Relationships between Tables

文章来源：http://allenbrowne.com/casu-06.html

原文：

Database beginners sometimes struggle with what tables are needed, and how to relate one table to another. It's probably easiest to follow with an example.

As a school teacher, Margaret needs to track each student's name and home details, along with the subjects they have taken, and the grades achieved. To do all this in a single table, she could try making fields for:    Name

    Address

    Home Phone

    Subject

    Grade

But this structure requires her to enter the student's name and address again for every new subject! Apart from the time required for entry, can you imagine what happens when a student changes address and Margaret has to locate and update all the previous entries? She tries a different structure with only one record for each student. This requires many additional fields - something like:    Name

    Address

    Home Phone

    Name of Subject 1

    Grade for Subject 1

    Name of Subject 2

    Grade for Subject 2

    Name of Subject 3

    ...

But how many subjects must she allow for? How much space will this waste? How does she know which column to look in to find "History 104"? How can she average grades that could be in any old column? Whenever you see this repetition of fields, the data needs to be broken down into separate tables.

The solution to her problem involves making three tables: one for students, one for subjects, and one for grades. The Students table must have a unique code for each student, so the computer doesn't get confused about two students with the same names. Margaret calls this field StudentID, so the Students table contains fields:    StudentID  a unique code for each student.

    Surname    split Surname and First Name to make searches easier.

    FirstName

    Address    split Street Address, Suburb, and Postcode for the same reason.

    Suburb

    Postcode

    Phone

The Subjects table will have fields:    SubjectID  a unique code for each subject. (Use the school's subject code)

    Subject    full title of the subject

    Notes      comments or a brief description of what this subject covers.

The Grades table will then have just three fields:    StudentID  a code that ties this entry to a student in the Students table

    SubjectID  a code that ties this entry to a subject in the Subjects table

    Grade      the mark this student achieved in this subject.

After creating the three tables, Margaret needs to create a link between them, by selecting the Database Container window, and choosing Relationships from the Edit menu. ("Grades" will be the "related table" in relationships with both the other tables.)

Now she enters all the students in the Students table, with the unique StudentID for each. Next she enters all the subjects she teaches into the Subjects table, each with a SubjectID. Then at the end of term when the marks are ready, she can enter them in the Grades table using the appropriate StudentID from the Students table and SubjectID from the Subjects table.

To help enter marks, she creates a form, using the "Form/Subform" wizard: "Subjects" is the source for the main form, and "Grades" is the source for the subform. Now with the appropriate subject in the main form, and adds each StudentID and Grade in the subform.

The grades were entered by subject, but Margaret needs to view them by student. She creates another form/subform, with the main form reading its data from the Students table, and the subform from the Grades table. Since she used StudentID when entering grades in her previous form, Access links this code to the one in the new main form, and automatically <

txdsdn

kjkjkj