Database Design: Students and their contacts

You are quite right that contacts should be a single table, but I would not relate this solely to families.  The relationship type between students and contacts is many-to-many, as is the relationship type between families and contacts.  A many-to-many relationship type is modelled by a table which resolves it into two or more one-to-many relationship types.  Consequently a single Contacts table can be related independently to Students and Families by two tables, one with foreign keys StudentID and ContactID, the other with foreign keys FamilyID and ContactID.  Other non-key columns in the two tables can differ of course to reflect the different attributes of the two relationship types, e.g. the point you make with regard to emergency contact data.

The nature of a relationship between a student and a contact, or between a family and a contact is an attribute of the relationship type, so this should be column in the table modelling the relationship type.

Diagrammatically the model would be as follows:

Contacts----<FamilyContacts>----Families----<Students----<StudentContacts>----Contacts

Note that in the above model there are two instances of a single Contacts table, not two separate tables.

It is possible that the same person might relate to a family or a student in two different capacities.  The primary keys of the FamilyContacts and StudentContacts tables are therefore composite keys made up of the FamilyID, ContactID and ContactType columns in one case, and the StudentID, ContactID and ContactType column in the other case.

Rather than address your specific questions I am going to describe the underlying basis of the model as, with a solid understanding of this, you'll find that the answers to the questions should be self evident, which is really what Richard Feynman is saying to his physics students in the quote below my signature.

The key to a robust and efficient relational database is the logical model, and its physical representation as a set of related tables, so I'll repeat the diagrammatic representation of this which I gave earlier, though it should be noted that this does not include all tables as there will obviously be the need for other referenced tables, e.g. Cities and States for address data.

Contacts----<FamilyContacts>----Families----<Students----<StudentContacts>----Contacts

Central to the model is the one-to-many relationship type between Families and Students, each family having one or more members as students, so the FamilyID column in Students is foreign key which references the FamilyID primary key column of Families.  In the students form the control bound to the FamilyID column is a combo box in which you'll see the names and other distinguishing attributes of each family listed, but whose value is the hidden numeric FamilyID value.  This is one of the most common types of interface object you'll find in relational databases as the basic one-to-many relationship type which it represents is found so commonly.

If you look at the model you'll see that either side of this central student/family core is a symmetrical pair relationship types, a many-to-many relationship type between students and their contacts on the right, and a many-to-many relationship type between families and their contacts on the left.

'Contacts' in this model represents anyone who bears any relationship to a student or family in any capacity, so it could be parent or guardian of a student, a teacher of a student or any sort of contact of a family such as their medical advisor, lawyer, etc.

As I've shown it the contacts table would cover all of the 'contacts' in a single table, so its columns would represent attributes of all types of contacts.  Attributes such as FirstName, LastName etc are obviously common to all contacts, whatever the type.  However there may be some attributes which are relevant to a subset of the contacts.  Parents or Guardians are a case in point as these have an attribute Relationship (not to be confused with a relationship in the database) with values Father, Mother and others in the case of students whose guardians are not their parents.  In database terms Parents and Guardians is a subtype of type Contacts, and the way to model this is by a one-to-one relationship type between the Contacts table and the ParentsOrGuardians table, i.e. the primary key of the latter is also a foreign key referencing the primary key of the former.  Note that this allows the same person to be a member of two or more sub-types of Contacts.  A friend of mine in primary school had his father as his teacher, so Mr Black would be represented in both the ParentsOrGuardians table and the Teachers table, in each case referencing the same row in the Contacts table, whereas Mrs Black would be represented only in the ParentsOrGuardians  table, referencing a different row in Contacts.

You said earlier that you proposed to have a family table with columns for mother and father, but you cannot rule out the possibility of a student's mother and father being members of separate families, i.e. the parents have divorced or separated and are now living in other family units.  Some years ago I helped out with a database for a children's hospice, and found this to be a relatively common situation.  In this scenario each parent would map to a separate row in Families via the FamilyContacts table.  I've assumed that each student record would reference only one family, however, by means of a foreign key FamilyID column in Students, on the basis that in a case like this one family would be regarded as the students principal family unit.  You might need to consider the validity of that assumption.

Some of the other questions you've raised, such as the references to those parts of my earlier post dealing with unbound text boxes referencing columns of a combo box, and the use of the NotInList event procedure, relate to the basic nuts and bolts of form design in Access, which a study of any competent book on Access or online resource should answer.  A sound knowledge of these sort of basic building blocks is something you really need to acquire before embarking on the development of your application.

Hmm, this might help...

http://allenbrowne.com/AppHuman.html

1.  Just to confirm, when I mentioned that all contacts go in one table, I meant all "People", whether they be a student, teacher or guardian. Please confirm that this is what you understood as well.

2.  What is the process of entering "People" into the DB?

3.  However, now that each contact in the DB will be added into the contact table. What will make the unique key for each person? If I have 2 John Smiths, what will distinguish them? Not the address or phone number or parent's names etc. since all that is in a different table.

4.  Are parents supposed to be entered as separate "People"?

1.  In some contexts that would be appropriate, but I would not recommend it in this case.  As the database is specifically student oriented, I would treat Students and Contacts as separate entity type, modelled by separate tables, as in the model I suggested above.

2.  You simply insert a row into the Students table via a bound form.  This form will contain a combo box bound to the FamilyID foreign key column and a control to enter the type nature of the relationship.  This should also be a combo box referncing a ContactTypes table.  This form would include a subform bound to the FamilyContacts table.  This subform have a single control,  a combo box bound to the ContactID column.

The students form would also include a  subform bound to the StudentContacts table.  Again including a single control,  a combo box bound to the ContactID column.  

3.  To differentiate between your two John Smiths etc the Contacts table should have an autonumber ContactID primary key column.  The combo boxes in the family contacts and student contacts subforms should return not only the contact's name, but other data, e.g. address data.  This can be done by returning multiple columns in the combo box's drop down list by including the relevant tables in the RowSource query.  To show the data from the additional columns when a selection is made you can, if you wish, include unbound text box controls in the subform which reference the Column property of the combo box.

When using combo boxes to select data form a referenced table such as Contacts, normally the control's NotInList event procedure would be used to insert a new row into the referenced table.  With personal names, however, this is not really possible because of the legitimate duplication of names.  You'll find an example of how to handle this in NotInList.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

The opening form in this little demo file uses a combo box with a multi-column drop down list, and its NotInList event procedure can be used to insert a new contact if no other contact of that name is in the database.  It also includes a command button to insert a new contact whose name is already present in the database.

4.  I would just treat them as family contacts.

@Gina, thanks for the link - very interesting examples - I am still digesting...

At first I thought I understood it right away, but after downloading the file and trying to understand... I got lost!

Mainly, I think, for the same reason I will describe below.

@Ken, thank you! However, I didn't really understand what has to be done... :(

I am not sure where to start, but I will try with this:

First of all,

Just to confirm, when I mentioned that all contacts go in one table, I meant all "People", whether they be a student, teacher or guardian. Please confirm that this is what you understood as well.

Secondly - and Mainly:

What is the process of entering "People" into the DB?

I.e., till now my thinking was as follows:

Each time a new student is added to the DB, we first check to see if his family exists, if yes, then we just link him to that family and enter his personal data (FirstName, DOB etc.). If not, then we start by creating a new family. Then we add the student as said previously.

However, now that each contact in the DB will be added into the contact table. What will make the unique key for each person? If I have 2 John Smiths, what will distinguish them? Not the address or phone number or parent's names etc. since all that is in a different table.

What is the starting point?

One more thing:

Are parents supposed to be entered as separate "People"?

I was planning on making a "Family" table which consists of LastName, FatherName, MotherName etc.

Is this wrong? Or better asked, is it better to insert them as people and then reference them together with the child? (which would technically be a lot more work on input....)

Sorry if this is a lot of questions.....

I hope this is clear.....

Thanks,

David

P.S. This question might be similar to what I asked in my previous thread, but I am not sure so please forgive me - I'm just confused and trying to find my way.... with this great community's help, that is!