Sign in to follow this  
Followers 0
wormpicker

Novice question: handling multiple department "positions"

9 posts in this topic

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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:

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • How to manage hundreds of fields and their related records
      By Jo_In_Oz
      Is there a limit to how many fields a table should have?
      My solution requires the management of 6 categories. Each category has up to 34 sub-categories, although most have 9-12, and each sub-category has several items requiring data entry. This leads to 91 sub-categories and hundreds of fields requiring data entry. The records per sub-category require relationships to corresponding sub-categories that respond to the data recorded. It is like a question/answer system, so the initial question field has data entered that requires answers entered and/or available (from previous experience) in the corresponding answer table. I have been thinking that I need to have the 91sub-categories in their own tables to limit the number of fields per layout. Is this a limitation? Is there anything wrong with having loads of fields in one table? Each of the 91 sub-categories will need to be related to the entity asking the question and receiving the answer. The questions and answers will eventually be presented in a report. In the future, when the answers have been acted on, the process is redone using the same sub-categories and the latest report is used to show progress, or otherwise.
      I could create tables for each main category and split the one with 34 sub-categories into 3, making 8 tables populated with the fields from the sub-categories which would result in approximately 50 to 100 fields in each table.
       
    • Can you link together Field name + field from one table to two fields in another table
      By Jimstrange
      I am working with a Woocommerce installation and trying to create a product form interface. Woocommerce stores product attributes as meta_key's and values. I was wondering if there was a way of linking the table to a different table which would be more manageable and easier to use.
       
      Below I have tried to illustrate the two different tables and was wondering if there was a way of linking them together allowing them both to update when information is entered. I have a feeling I might need to use global fields and calculations. However, not completely sure if it is possible.
       
      Woocommerce Table
       
      ID post_id meta_key meta_value 1 1 Field 1 Value 2 1 Field 2 Value 3 1 Field 3 Value 4 2 Field 1 Value 5 2 Field 2 Value 6 2 Field 3 Value  
      New Table, which should populate existing Woocommerce Table
       
      ID post_id Field 1 Field 2 Field 3 1 1 Value Value Value 2 2 Value Value Value I hope this makes sense and any advice will be greatly appreciated 
    • Related table picking up 0 values
      By enquirerfm
      I have a table which is related to another one. Trying to keep it simple lets say one is a table of cats (first table) which has fields for cat species, colour, weight etc. I can also record the details of when I bought the cat, price paid etc. It is linked to another table where I record all the prices of cats I can find (second table) and they're matched according to species and colour.
      I create a report based upon a time period using the first table and by doing a find of records which match certain criteria I wish to includewhich shows certain data for example: min price, max price, av. opening price, av. opening price. The report doesn't need a script it is simply another layout which pulls the data from certain calculations fields some of which are based upon the other table (eg a summary field). The problem is the table (first table) picks up references to any cats given away (eg $0) as part of its minimum search and I only want to include cats whose price is at least $50. Also, I wish to exclude the $0 values in the summaries of average prices in the second table.
      I suppose what it comes down to is how can I ensure 'summaries' never include 0 values without just excluding them from the DB altogether (I could do a find on the price field for $0 and then delete all these records).
    • Creating a button that copies data from one table record to another.
      By MutantST
      Hi All,
      I would like to create a button that copies data from specific table and fields to other ones on display in my form. The context for this is that I am creating a button that copies data that has already been entered and displayed in some text merge fields. i.e., Account address (first table), and I would love to have that data copied to the location address fields (second table).
      See attached file.
      Any help would be gratefully received (Complete beginner).
       
      Cheers, Simon
      DB_SC_Grab- button question.tiff
    • Not working - Importing all Tables in a FileMaker File into Another FileMaker file
      By Linda L
      Environment:  Windows 7 with FileMaker Pro 14.
      I have a test database that I am currently making changes to.  I saved the test database as a clone, so that I could test importing data back into it.  the Import functions allows me to import the first table of information just fine, but it will not let me import any other tables.   On any subsequent import, I can select the source table from my source file, but all of the target files are grayed out, except for the first file to which I was able to import into.  What am I doing wrong?  I will need to do this on a regular basis, as I will use this technique to move my changes into the live environment.
      Thanks,
      Linda