Novice question: handling multiple department "positions"

[wormpicker]

My first post here from a relative novice, so I apologize is this is a dumb question or one that has been covered many times.  I'm starting to build a database for an academic department, to keep track of contact information, as well as publications, grants, applications, and other items associated with the different department members.  Department members can be one of several different positions or "ranks," such as faculty (professors, associate professors, etc.), post-docs, grad students, staff etc.  I also want to keep track of the relationships between these various department members, for example, which grad students "belong to" which faculty member.  Ok, now my question:  Should I keep everyone in one "Department Member" table that has a field denoting each member's position, or should I use a separate table for faculty, grad student's, post-doc's etc.  My intuition tells me I should use one table for everybody, to avoid duplicating lots of contact fields.  But if I do that, how would I relate grad students, for example, to professors?  Would I create separate table occurrences of the Department Member table to use for each position (faculty, grad student, etc.), and relate those different table occurrences?  Many thanks for advice.

[Fitch]

Yes, keep everyone in one "Department Member" -- or better IMO, "Person" -- table. Make a separate table for "relationship" or "role." You would then use this second table as what's known as a "join table" to connect with another table occurence of Person.

[wormpicker]

Thank you!

[wormpicker]

Pardon the follow-up...  Would I have a single "Role" join table, which contains, for example a field that can contain the data "Professor" "Grad Student" "Postdoc"or "Staff" etc.  Or would I use a separate join table for each of those different roles?  Thanks again.

[eos]

Pardon the follow-up...  Would I have a single "Role" join table, which contains, for example a field that can contain the data "Professor" "Grad Student" "Postdoc"or "Staff" etc.  Or would I use a separate join table for each of those different roles?  Thanks again.

 

Then you'd have tables that would be called Professors, GradStudent, and Staff, instead of a Roles table. Were you planning to introduce two People tables, one for female and one for male persons? 

 

OK, seriously, such a table is called Roles plural because people can appear several times over in different roles – and this table will be a focal point for several other (parent) entities. Imagine you want to compile a faculty listing, or a resumé; you'd have to examine each of these single-role tables to find all (current) people of a faculty, or all roles of a given person, instead of looking into one table.

 

By the same token, an invoice solution has one table for line items, not one table per product in a line item, or one line items table for each invoice …

[wormpicker]

Ok, great, thanks.  As I said, I'm a novice.  Hopefully it will all come together for me as I build it.

[Fitch]

I don't know -- you could conceivably employ a join table that's just for linking profs to grad students. We're talking about a finite number of possible joins. Shoot for The Simplest Thing That Could Possibly Work.

[bcooney]

People>Peo_Role<Roles

 

There are three tables, and the middle table is the join table. It contains all the combos of People and Roles. Many people may have just one Role. I sometimes name the right table Roles Library. That is, it is a table of all the available role choices.

 

Invoices>Line Items<Products, where the join is Line Items.

 

Project>Team<Roles

A person may play several roles on a project team. They may be the Project Manager, as well as the Lead Developer (lol).

[Fitch]

Barbara, your suggestion is fine, but I think it adds needless complexity in this scenario. I wouldn't think the roles here tend to change much, nor are they numerous, nor do they have a bunch of other attributes (not as sure about that last one). Not worth another table IMHO. A good old-fashioned value list is sufficient, at least for starters.