Jump to content
Sign in to follow this  
wormpicker

Novice question: handling multiple department "positions"

Recommended Posts

wormpicker    0

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
Fitch    148

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
wormpicker    0

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
eos    225

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
wormpicker    0

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
Fitch    148

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
bcooney    101

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
Fitch    148

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  

  • Similar Content

    • By NewBoard
      I have a table that contains dimensional data for parts. Each record has a unique ID, but also has another field with the ID associated with the part. I have another table that I'll be storing individual inspection data on. I was wondering if it's at all possible to parse the first table, and copy over all records that have a specific part ID to the second table whenever someone starts a new inspection record.
      Please let me know if I need to provide any more information and/or pictures.
      Thank you in advance for any assistance you can offer!
    • By 123
      Hey,
      I have a Tab-Separated Text File which I want to import to a database that I just created in FileMaker Pro 15. The text file essentially is a log of events, for instance, one line describes an order, another line describes a payment that has been made. The problem is that I somehow need to import all these lines into different tables. I want all lines in the log file that describe a payment in a table called Payments, and all lines that describe an order in a table called Orders for example.
      I'm just starting out with FileMaker so forgive me if there is an obvious solution that I'm not aware of. I have a background in software development, so the only solution that I can think of is writing a small program myself that does all the parsing for me and creates separate text files that contain all the orders and payments and other events that have been logged in the file. Then I could simply import each file into the table I need all the data in. 
      So to sum everything up, my main question is if there is a way to somehow parse the text file before importing it. I know that you can create custom scripts in FileMaker but I'm not sure if what I need to do can be done just with that.
      Thanks in advance,
      Mike
    • By Richmilnix
      My goal here is to write a database with tidier relationships than I've used in the past, so I'm intending to use discrete table occurrences of the same data. Here's the roadblock:
      In layout 2_week_schedule, I have 14 portals that display a staff schedule (two weeks' worth) and can't be edited. My goal is that a logged-in administrator can click on the date field of any one of those fourteen days and go to a layout 1_day_schedule that is displaying the same information, though with more details and one day at a time.
      The underlying table of each layout is the same, though the layouts are based on different TOs.
      I wrote this with a script trigger in the date field in 2_week that fires a script to copy its value as date to variable $date, switch to the 1 day layout, and set the pertinent date field there to variable $date (and then commit).I don't know if it's my error, but I think of variables as being like my computer's clipboard - any value can be copied & pasted to/from. But the script fails with the message The operation could not be completed because the target is not part of a related table.
      I did try a simple redundant relationship (connecting the two pertinent fields), but that was a hail Mary, and didn't do the trick anyway. Is there a better way to accomplish what I want? In short, I want administrator who's scrolling through the existing schedules to be able to look at any date where she wants to make a change, click on it, grab its contents, and be brought to a more flexible interface where its contents get inserted into a portal that will then show her the results.
      This file is web-accessible, so I can post its address if it helps to see what I'm talking about.
    • 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.
       
    • 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 
×

Important Information

By using this site, you agree to our Terms of Use.