Wraping my head around a people table vs a role table...

[Jeff Albro]

I've got a basic understanding of how a relational database works with primary keys etc. I've got an issue that is bugging me and there must be a solution I'm missing.

I want to build a database for a university department. To start off with, it will contain people (faculty, staff, grad students, undergrad students) rooms, computers, programs etc.

I want to associate rooms and computers with people, but only SOME people - faculty, staff, or grad students, NOT undergrads. Same with rooms.

Basically, I want to limit the drop down lists.

And undergrads and grads, they should have faculty advisers, but I need the drop down lists to only show faculty.

Student (both grad and ugrad) layouts will have drop down lists for only faculty members.

The resultant problem is that I want to link various items to various people, but each item should only link to certain TYPES of people.

I could have a big "people" table or separate "staff", "faculty" etc tables. It the first situation, I don't know how to limit the drop downs to show only appropriate people, and in the second, if I want to pull drop downs from two tables, I don't know how to do that.

Is my issue clear?

Ideas?

Thanks!

-Jeff

Edited December 14, 2007 by Guest
Edited for clarity.

[Vaughan]

"I want to associate rooms and computers with people, but only SOME people. Basically, I want to limit the drop down lists."

Tell us what the problem is, not how you think it should be solved.

"I need a database to track which AV equipment is loaned out to students and lecturers" or

"I need a database to schedule note-takers and interpreters for disabled or ESL students"

[Jeff Albro]

Goal: Build a database that allows us to keep track of pretty much everything in the school.

The problem is I need to associate various things (rooms, computers, departments) with varying TYPES of people (sometimes just faculty, sometimes faculty and staff, sometimes faculty, grad students, and staff).

How do I set up the value lists in a sensible way?

I'm learning about filtering tables by using self joins based upon values, and then using those TO's to limit the value lists. Of course, I only have the vaguest idea of what I just said.

I'm looking at the "My School" sample database from here:

http://www.kevinfrank.com/demo-files-78.html

-Jeff

[Fenton]

What Jimmy Jones did there is pretty much exactly what you need. The basic principle is the use of "text" matches to filter the value lists, matching the word "teacher" in a "fixed-value" field to the word "teacher" in a user-entered Role field.

The Role field may be in any table. In this case it's in the People table. That is basically saying, "Each person has 1 and only 1 role," which is the simplest case.

The brilliant part of the way he's done it is to use a Globals table (and fields) to put the fixed-value fields in. That way the value is available from anywhere. (You would need to re-populate the fields if you used Save As Clone.)

There is a required option when creating a related Value List, to say where to evaluate the relationship from. By specifying a global table of global fields as the "evaluate from," you've made that filtered value list available for use by any other table.

So, this is not really a "self-join," as he's used a separate globals table. Yes, you could do much the same thing using self-joins all within People, using global fixed-value fields. But using a separate Globals table is less messy.

You are going to want to add a few more fixed-value fields, instead of just one "teacher". But other than that his method would work fine, as far as I can see.

As far as the "1 People table" or "separate Staff and Student tables", I would probably go with the 2 tables. There is no "overlap," and it makes regular list views easier for regular folks. Though, it's really a matter of preference, as either structure will work.

Also, as a matter of preference, I would likely create a separate table occurrence group for the Global table, using more of an "anchor-buoy" Relationship Graph organization. And I'd use a calculation field with global storage for the fixed values. I don't remember when that option appeared, but it may not be available in FileMaker 7 (I'm a little fuzzy on 7 as the years go by).

[Jeff Albro]

What Jimmy Jones did there is pretty much exactly what you need. The basic principle is the use of "text" matches to filter the value lists, matching the word "teacher" in a "fixed-value" field to the word "teacher" in a user-entered Role field.

The Role field may be in any table. In this case it's in the People table. That is basically saying, "Each person has 1 and only 1 role," which is the simplest case.

I'm hoping that is the case. However, I am not convinced that the "business logic" in my department allows for that.

The brilliant part of the way he's done it is to use a Globals table (and fields) to put the fixed-value fields in. That way the value is available from anywhere. (You would need to re-populate the fields if you used Save As Clone.)

There is a required option when creating a related Value List, to say where to evaluate the relationship from. By specifying a global table of global fields as the "evaluate from," you've made that filtered value list available for use by any other table.

I understand the rough idea here... I think... When creating the filtered value list it says "Include only related values starting from (table name)". Is another way to put this as "Include only those entries that have a relationship to (table name)"?

Uh-uh... this is starting to make sense... is that a bad thing?

So, this is not really a "self-join," as he's used a separate globals table. Yes, you could do much the same thing using self-joins all within People, using global fixed-value fields. But using a separate Globals table is less messy.

I understand why this is not a self-join.

You are going to want to add a few more fixed-value fields, instead of just one "teacher". But other than that his method would work fine, as far as I can see.

Yeah, but I would like to have multi-role value lists.... a value list of Faculty AND Staff... I see no way of doing that with this set up. I can't "Include only related values starting from (table name)" for two tables... Is there a way to concatenate tables? I mean EVEN if a user can have only one role, I still need to be able to select from a combination of roles.

As far as the "1 People table" or "separate Staff and Student tables", I would probably go with the 2 tables. There is no "overlap," and it makes regular list views easier for regular folks. Though, it's really a matter of preference, as either structure will work.

I see what you are saying, but I'm not sure the business logic will work out yet.

Also, as a matter of preference, I would likely create a separate table occurrence group for the Global table, using more of an "anchor-buoy" Relationship Graph organization.

Understood.

And I'd use a calculation field with global storage for the fixed values. I don't remember when that option appeared, but it may not be available in FileMaker 7 (I'm a little fuzzy on 7 as the years go by).

You lost me on this one. I'm using 9.

Thanks so much!

-Jeff

[Fenton]

1. I realized also that you likely want to keep People together; because of graduate students, who are also teaching assistants. They may overlap, so that means one table.

2. In order to match both "Faculty" and "Staff" (assuming faculty is not already included within staff; but anyway), you just need a multi-line global (or global calculation).

If global, just enter:

Faculty

Staff

If a calculation with global storage, enter:

"Faculty" & ¶ & "Staff"

A relationship matches either of its lines.

3. (•) Global storage is a new option for calculations. It's above ( ) Do not store calculation results.

The advantage is that it's available everywhere, without a relationship, whereas (•) Do not store is not.

As I said, you can just use global fields (ie., Text field, with (•) Storage global. But then if you save a Clone, you lose them. A rare occurrence perhaps, but I prefer the calculation.

[Jeff Albro]

1. I realized also that you likely want to keep People together; because of graduate students, who are also teaching assistants. They may overlap, so that means one table.

Yup. We do have staff that are students.

2. In order to match both "Faculty" and "Staff" (assuming faculty is not already included within staff; but anyway), you just need a multi-line global (or global calculation).

If global, just enter:

Faculty

Staff

If a calculation with global storage, enter:

"Faculty" & ¶ & "Staff"

A relationship matches either of its lines.

So this is is relationship matching based upon a repeating field, right?

What if the field consists of:

Faculty

Student

Staff

Will a global of:

Faculty

Staff

Still match? Or is there a "contains" operator? I tried to do that yesterday but couldn't seem to get it to work for the sample file.

Actually, I was planning on creating a bunch of boolean fields like Is_Faculty, Is_Student or something to that effect. Then I found out that FileMaker doesn't handle boolean fields. Bummer.

I might use individual text fields instead.

I am concerned about repeating fields as I may need to export with ODBC. I might want to put a faculty directory on our website. I know about the php doohickeys, but I will be working with other developers who are comfortable with SQL queries against MySQL, and I'd like to be able to mimic that approach.

3. (•) Global storage is a new option for calculations. It's above ( ) Do not store calculation results.

The advantage is that it's available everywhere, without a relationship, whereas (•) Do not store is not.

As I said, you can just use global fields (ie., Text field, with (•) Storage global. But then if you save a Clone, you lose them. A rare occurrence perhaps, but I prefer the calculation.

This is because when you clone, you lose the record contents, but not the field descriptions, which include calculations, correct?

Thank you very much for your help, Fenton!

-Jeff

[Fenton]

So this is is relationship matching based upon a repeating field, right?

No. These are return-separated values, ie., words on lines. That is what relationships work with (as well as records, which, in a relationship, are a return-separated index(es). There is no need for Booleans or other calculations (BTW, FileMaker 9 has a GetAsBoolean() function, in the Logical functions section).

Make your Role field a checkbox field (which is return-separated values), or use a join table (I don't think that's really required). Then relate your global fixed-value fields to it.

"Faculty" & ¶ & "Staff" is just a calculation producing:

Faculty

Staff

It will match:

Faculty

or

Staff

or

Faculty

Whatever

or

Staff

Whatever

[Fenton]

This is because when you clone, you lose the record contents, but not the field descriptions, which include calculations, correct?

Exactly. Once you type it into the calculation it's there forever, while still available everywhere as a global value.