Jump to content

  •  

Photo

Novice question: handling multiple department "positions"

tables table occurrences personel

  • Please log in to reply
8 replies to this topic

#1 wormpicker  newbie

wormpicker
  • Newbies
  • PipPipPipPip
  • 4 posts
  • FM Application:13
  • Platform:Mac OS X Mavericks
  • Skill Level:Novice

Posted 15 April 2014 - 07:17 AM

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.


  • 0

#2 Fitch  Imaginary friend

Fitch
  • Moderators
  • 4,051 posts
  • LocationPortland, Oregon
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 12, 13
  • Membership:TechNet
  • Time Online: 16d 18h 39m 11s

Posted 15 April 2014 - 03:32 PM

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.
  • 0
Tom Fitch :: Portland, Oregon :: Fitch & Fitch: FileMaker consulting

#3 wormpicker  newbie

wormpicker
  • Newbies
  • PipPipPipPip
  • 4 posts
  • FM Application:13
  • Platform:Mac OS X Mavericks
  • Skill Level:Novice

Posted 16 April 2014 - 08:52 AM

Thank you!


  • 0

#4 wormpicker  newbie

wormpicker
  • Newbies
  • PipPipPipPip
  • 4 posts
  • FM Application:13
  • Platform:Mac OS X Mavericks
  • Skill Level:Novice

Posted 16 April 2014 - 09:04 AM

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.


  • 0

#5 eos  Paris 2016 … :-)

eos
  • Members
  • 1,147 posts
  • LocationTegernsee, Bavaria
  • FM Application:13 Advance
  • Platform:Mac OS X Mavericks
  • Skill Level:Intermediate
  • Membership:TechNet
  • Time Online: 217d 18h 28m 55s

Posted 16 April 2014 - 09:44 AM

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?  :laugh:

 

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 … :cry:


  • 0

#6 wormpicker  newbie

wormpicker
  • Newbies
  • PipPipPipPip
  • 4 posts
  • FM Application:13
  • Platform:Mac OS X Mavericks
  • Skill Level:Novice

Posted 16 April 2014 - 09:48 AM

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


  • 0

#7 Fitch  Imaginary friend

Fitch
  • Moderators
  • 4,051 posts
  • LocationPortland, Oregon
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 12, 13
  • Membership:TechNet
  • Time Online: 16d 18h 39m 11s

Posted 16 April 2014 - 10:39 AM

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.


  • 0
Tom Fitch :: Portland, Oregon :: Fitch & Fitch: FileMaker consulting

#8 bcooney  consultant

bcooney
  • Moderators
  • 5,788 posts
  • LocationLong Island, NY
  • FM Application:13 Advance
  • FMGo:iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:9, 10, 11, 12
  • Membership:TechNet
  • Time Online: 24d 53m 2s

Posted 16 April 2014 - 10:40 AM

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).


  • 0

#9 Fitch  Imaginary friend

Fitch
  • Moderators
  • 4,051 posts
  • LocationPortland, Oregon
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 12, 13
  • Membership:TechNet
  • Time Online: 16d 18h 39m 11s

Posted 16 April 2014 - 10:46 AM

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.


  • 0
Tom Fitch :: Portland, Oregon :: Fitch & Fitch: FileMaker consulting





FMForum Advertisers