Table design question

[Lars Brownies]

I'm trying to improve my database design.

Example: in a database you have a table Students and a table Teachers. Both
have fields like FirstName, LastName, DOB, etc.

From a datamodeling perspective I assume you should have one Person table
with relation tables in which the roles (student or teacher) would be
stored.

From an application design perspective I see benefits in having separate
tables for Students and Teachers and I think this is what you most see in
practice.

Are my assumptions correct?

In this respect, if Students and Teachers both have addresses directly
linked to them should you have also 2 address tables or just one that links
to both the Student and Teacher table?

Thanks,

Lars

 

[Arvin Meyer [MVP]]

No. People are people, no matter what role they play. A student may
eventually become a teacher. Do you really want to reenter the details?

If that happens you would want a PersonRoles table as a many-side table. You
now only need a single address table.

 

[John W. Vinson]

I'm trying to improve my database design.

Example: in a database you have a table Students and a table Teachers. Both
have fields like FirstName, LastName, DOB, etc.

From a datamodeling perspective I assume you should have one Person table
with relation tables in which the roles (student or teacher) would be
stored.

From an application design perspective I see benefits in having separate
tables for Students and Teachers and I think this is what you most see in
practice.

Are my assumptions correct?

In this respect, if Students and Teachers both have addresses directly
linked to them should you have also 2 address tables or just one that links
to both the Student and Teacher table?

Thanks,

Lars

In general I'd agree with Arvin that "people are people". This is a grey area
though! It depends to some extent on what kind of Entity you're modeling: if
the only things that teachers and students have in common (from a data
modelling perspective) is names, and you store a lot of information about
Students and a lot of *different* information about Teachers, you could make a
good case that they're different. For example, if you need DOB,
ParentGuardian, TransportationMode, EmergencyContact, Grade for students (none
of which are relevant for Teachers) and InsurancePlan, TenureStatus, HireDate,
UnionAffiliation for teachers then you may want either separate tables, or a
"Subclassing" setup (with a central People table related one-to-one to
StudentData and TeacherData tables).

 

[Lars Brownies]

Thanks John, Arvin,

though! It depends to some extent on what kind of Entity you're modeling:
if
the only things that teachers and students have in common (from a data
modelling perspective) is names, and you store a lot of information about
Students and a lot of *different* information about Teachers, you could
make a
good case that they're different.

In this case the Persons table fields *could* be applicable for Students and
Teachers, but, since Student is the main entity in the application, all
fields are necessary for Students but only part of the fields (LastName,
LastNamePrefix, FirstName) are filled for Teachers. The fields we store
extra for Students are for instance, DOB, Place of birth, Social Security
Number, Educationlevel.

There are other Many tables that apply to both Students and Teachers, like
(work)address, phonenumber, emailaddress.

This makes me assume that I need a central Person table, like you described.
I'm still doubting about a related one-to-one table for the StudentData. If
I store it all in one table I have a lot of fields left blank for Teachers,
but if I separate the data I would for instance have to move the DOB field
to another table which doesn't feel right.

Any hints?

Lars

 

[John W. Vinson]

This makes me assume that I need a central Person table, like you described.
I'm still doubting about a related one-to-one table for the StudentData. If
I store it all in one table I have a lot of fields left blank for Teachers,
but if I separate the data I would for instance have to move the DOB field
to another table which doesn't feel right.

That's the kind of decisionmaking process that makes database normalization
difficult. You have four (or more) choices:

1. tblPeople, with some fields which are optional for some classes of people
2. tblPeople, with fields pertinant to both teachers and students, related one
to one to tblStudents with fields pertaining only to students
3. The same but with tblTeachers
4. tblPeople, related one to one to both tblStudents and tblTeachers

All of these options have their plusses and minuses, and you need to make a
judgement as to which is most appropriate for the data you'll be managing.
They can all four be made to work well, just with different implementation.

 

[Paul Shapiro]

That's the kind of decisionmaking process that makes database
normalization
difficult. You have four (or more) choices:

1. tblPeople, with some fields which are optional for some classes of
people
2. tblPeople, with fields pertinant to both teachers and students, related
one
to one to tblStudents with fields pertaining only to students
3. The same but with tblTeachers
4. tblPeople, related one to one to both tblStudents and tblTeachers

All of these options have their plusses and minuses, and you need to make
a
judgement as to which is most appropriate for the data you'll be managing.
They can all four be made to work well, just with different
implementation.

One thing not discussed yet is the specificity of relationships. I would
assume only a teacher can teach a class, and only a student can enroll for
classes. So you might want the ability to have relationships that are
specific to teachers or students, and perhaps some relationships like the
address example you gave that apply to both. The only way I know to get that
flexibility is with the subcategory model: Person {personID, lastName,
firstName, etc.} with 1:1 relationships to Student {personID, DOB, etc.} and
Teacher {personID, certificationDate, etc.}. If a person can only be either
a teacher or a student you would have a Person.personType attribute too.

Unfortunately there isn't any built-in support to help with subcategory
relationships. You have to be careful to maintain the subcategory tables to
make sure you don't end up missing the child row. You can use cascading
deletes on the relationship to prevent orphaned child rows. With Access'
default approach to editing forms based on queries, you can use a
StudentFull query that joins Person and Student and a TeacherFull query that
joins Person and Teacher as form record sources. I seem to remember that
Access then does a pretty good job of creating the subcategory rows, as long
as you ensure some data is entered.

In this particular case, it seems to me that it might be easiest to have
separate Student and Teacher tables. It's rare that a student becomes a
teacher. Furthermore, I don't think you would ever care to match student and
teacher data even if they were the same person. Logically, there isn't any
overlap, so I don't see a data redundancy issue.

If you only store a single address for each person, I would put the address
fields in the Student and Teacher tables rather than in a child table. If
you store multiple addresses for both students and teachers, or have any
other child tables that apply to both types, that would probably change my
recommendation to the subcategory approach.

 

[Lars Brownies]

Thanks for the additions, John, Paul,

In the real life case I need to store more addresses per person and have
more child tables for both entities, so I'd have to go for the subcategory
approach.

Lars

 

[Lars Brownies]

An additional question. With the subcategory approach (with tblPeople and
tblStudent) should I give the student table it's unique student ID number
and link the specific student child tables to that student ID number? Or
should I link everything to the people ID_number and forget about a specific
student ID number? I guess the first option would be better since it
prevents users from (accidentally) entering student type of info for a
teacher.

Lars

 

[Lars Brownies]

Thanks! I think I understand .

By
"When it comes to other tables which are related to the people, for those
which have a relationship to both students and teachers, an enforced
relationship with teachers should be created"

didn't you mean
"...an enforced relationship with *people* should be created"

If so, than I know I understand

Lars

When modelling a Type/Sub-type (aka Class/Sub-class) relationship the
primary
key of each is the same, in this case PersonID for the tables People,
Students and Teachers. You can of course call the keys of Teachers and
Students TeacherID and StudentID if you prefer, but my own preference is
wherever possible to use the same name for each. The primary keys of the
latter two are also foreign keys referencing the primary key of people;
this
is what creates the one-to-one relationship type. Bear in mind, however,
that the relationship type is nevertheless directional from the Type to
the
Sub-type, so by enforcing referential integrity a row can only be inserted
into a table modelling a sub-type if a referenced row already exits in the
table modelling the (super) type. While the key of People can be an
autonumber, those of Students and Teachers cannot of course.

What characterizes a Type/Sub-Type model is that every sub-type shares the
attributes (and thus columns) of the (super) type, i.e. those which are
common to all such as people's names, but each sub type does not share any
attributes which are specific to other sub-types. An entity (person in
this
case) can of course be an entity of more than one sub-type. My own wife
is a
college lecturer, but she has to my knowledge also simultaneously enrolled
as
a student on at least one course at her own college.

When it comes to other tables which are related to the people, for those
which have a relationship to both students and teachers, an enforced
relationship with teachers should be created, while for those which relate
specifically to teachers or students an enforced relationship with either
Teachers or Students should be created. A CoursesTaught table would thus
be
related to Teachers and Courses, modelling a many-to-many relationship
between Teachers and Courses; a CoursesTaken would thus be related to
Students and Courses, modelling a many-to-many relationship between
Students
and Courses. In passing I'll point out that things do get a little more
complex here as if teacher A teaches course B, and student C takes course
B,
this might not necessarily mean that student C is taught by teacher A as
more
than one teacher might teach that course. There are ways around this by
normalizing to a more advanced level, but I think it would perhaps only
confuse matters to explore that avenue at present. We can always come
back
to it later if necessary.

With this model, attribute values relevant only to a teacher or a student
can
only be entered for a person who is a member of the relevant sub-type, and
data can only be entered in related tables where appropriate to the
sub-type
of which the person is a member, a teacher cannot be enrolled as a student
in
the CoursesTaken table for instance unless, as with my wife, they are also
represented in the Students table, which I think answers your last point
about accidental entry of inappropriate data.

Ken Sheridan
Stafford, England

An additional question. With the subcategory approach (with tblPeople and
tblStudent) should I give the student table it's unique student ID number
and link the specific student child tables to that student ID number? Or
should I link everything to the people ID_number and forget about a
specific
student ID number? I guess the first option would be better since it
prevents users from (accidentally) entering student type of info for a
teacher.

Lars

This makes me assume that I need a central Person table, like you
described.

[quoted text clipped - 57 lines]

have any other child tables that apply to both types, that would
probably
change my recommendation to the subcategory approach.