Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Many tables vs. just two tables dilemma

Question asked by happychap on Jul 29, 2010
Latest reply on Jul 30, 2010 by RickWhitelaw

Like • Show 0 Likes0
Comment • 8

Title

Post

Hello

Here's the situation. I have two tables: "Students" is just basic info on the student. "Records" has 100+ fields, since there are many types of records: observation records, phone call records, etc. The two are related by Student_Name (validated as unique value).

What works: (1) I have a button in each record that will take me to the appropriate layout for the type of record. (2) I can generate a report that outlines a student's whole record history, regardless of record type, in chronological order. I need this functionality.

My question: I wonder if I'd rather have my various types of records have their own dedicated tables? It seems like it would make navigating/editing/scripting/creating new types of records easier.

BUT, I don't want to lose my treasured chronological report of all records, regardless of type. How hard would it be to get a report that listed records in chronological order from various tables? Would I use lookups and have a "reports" table? Maybe that would be more trouble than it is worth?

I know there is something about using TO's here, but for the life of me I can't figure out how splitting it up into TOs really helps, or even how to do that...I have tried to read the TO article posted up here before and it is a bit, um, abstract.

Many thanks in advance,

Joe

No one else has this question

Outcomes

8 Replies

philmodjunk Jul 30, 2010 8:57 AM
Mark Correct
Correct Answer
I suspect that was my TO article and it can be a bit abstract for the new user. The thing to keep in mind is that the boxes in the relationship graph in Manage | Databases | Relationships and the tables listed in Manage | Databases | Tables are two different objects even though they have exactly the same name. Almost every where in Filemaker where you select a "table" you are really referring to a box in the relationship graph so filemaker can "know" how to link your current record to other related records.

Back to the main question: I think you want to keep your basic table structure here though there may be changes you can make to cut down on the "100+" fields. The combined table will be much easier to work with for your report. Here's some suggestions for making things easier you can evaluate for possible inclusion in your database:
1. You can have as many layouts that refer to the same table (oops table occurrence!) on your relationship graph as you want. Thus, you can create as many dedicated layouts for each type of "student record" as you want. You can use scripts and script triggers to ensure that a layout dedicated to Observation records, for example, only displays observation records to avoid confusion. This allows you to hide the fields not required for a given type of record from the user.
2. You can analyze your student records table and identify just those fields you need in your report. The remaining fields can be moved to separate related tables and these can be dedicated to specific record types.
Like • Show 0 Likes0
Actions
sunmoonstar.13 Jul 30, 2010 9:19 AM
Mark Correct
Correct Answer
"Records" has 100+ fields, since there are many types of records: observation records, phone call records, etc.

100+ fields in a single table? Hmm, that doesn't sound so good...

The two are related by Student_Name (validated as unique value).

That doesn't sound good either, because names aren't unique. What if you have two students with the same name? What if a student's name changes for some reason? Better to use unique serial numbers to relate records.

My question: I wonder if I'd rather have my various types of records have their own dedicated tables?

Yes, that would be a MUCH better solution, IMO, and it would allow you to do TONS of cool things with your data.

BUT, I don't want to lose my treasured chronological report of all records, regardless of type. How hard would it be to get a report that listed records in chronological order from various tables?

Records can be sorted in numerous ways, one of which is sorting by date, so it's dead easy to generate reports with a chronological list of records.

Splitting up your 100+ fields into related tables will take a bit of careful forethought and planning, but the effort will be worth it in the end.

Nick
Like • Show 0 Likes0
Actions
philmodjunk Jul 30, 2010 9:47 AM
Mark Correct
Correct Answer
"so it's dead easy to generate reports with a chronological list of records."

But without a unified table, reporting all types of student records in the same report--that's how I understood the original post--can be very difficult.

Good catch on the student_name relationship. Students of all ages do change their names at times nor are they unique.
Like • Show 0 Likes0
Actions
FentonJones Jul 30, 2010 10:48 AM
Mark Correct
Correct Answer
An idea for a simple "unified" table, to show results from multiple tables. The simplest is a Dates table, which is simply a record for each Date, starting whenever to whenever. It can fairly easily be created on the fly for a year, only 365 days per. You want to start it at the earliest date with data in any of the relevant tables.

Another alternative is the Dates per Student, where you'd have 1 record for each unique Student and Date combo. There are ways to get around this requirement however, using only the 365 dates per year structure, if you can set a global field in Dates to the Student's ID.

You can then point to other tables and get chronological reports from multiple tables. The results per date would need to be summarized, in cases where there are multiple per date; either Sum() for numeric, or List() for non-numeric (Substitute ", " for ¶ could list text on one line).

With the 365 dates method, you use calculations, the data is dynamic, still in its original table. With the other Date|Student combo method, you could fix "historical" data; but it will no longer be dynamic.

To get what you want, a chronological list for one student, using the 365 dates structure, you need to set a global in the Dates table to the StudentID, and include it in the relationships to the data tables. You have to do this, as you'd be passing "thru" the Dates table, and it has no IDs. This could be done via a script trigger attached to a Tab object (FileMaker 10+), or any navigation script.

To eliminate the dates where nothing happened for that student, you create a calculation field in Students, which uses the existing relationships to each table based on the Student ID, to List (relationship::date); getting only dates where the student has data. Put the date lists from the multiple tables together as one list, and use that to target only Date records where this Student has data. In other words, from Students, pre-filter the Dates table for relevant dates, then let the Dates table's global StudentID AND Date filter the other tables.
Like • Show 0 Likes0
Actions
FentonJones Jul 30, 2010 10:45 AM
Mark Correct
Correct Answer
Another approach entirely. Use the David Graham method, which uses a new central table, then satellite tables for each type of "record". It would be similar to the Student|Dates method, but cleaner. The central table's records would be created automatically upon date entry, so you wouldn't have to do anything really. It's a little bit more to set up however, but not much.
Like • Show 0 Likes0
Actions
happychap Jul 30, 2010 4:38 PM
Mark Correct
Correct Answer
Thanks all. I know I am supposed to use Serial #s to create the one-to-many (Students to Records) relationship. I have tried that in the past, and probably could have tried harder, but I kept getting five different Johnny Smiths in the Students table that way. Perhaps I should go back and make sure I am not allowing creation of records in students table? That would be good...

So, for the overall layout of the tables...thanks for all the suggestions. The student/date thing is cool, but some students have multiple records per day. Phil, if I go with your first post's second suggestion...keep the five common fields for the reports table, and spread the specific record types across their own tables...if I do that, how should I relate to the specific record-type tables from the "plain old record/report" table? Those would be one-to-one? What would the primary key and match fields be?

Thanks! Joe
Like • Show 0 Likes0
Actions
philmodjunk Jul 30, 2010 5:02 PM
Mark Correct
Correct Answer
First, the point to using a unique serial number is to prevent such duplicate "Johnny Smith" records. If you are getting those, something isn't set up correctly.

If you use dedicated detail tables for the different types of student records, the relationship would be one to one in most cases though you may find exceptions to that as this is not strictly necessary. (You can set up a detail table where each row in a portal is a different part of the total student record.)

Simply define an auto-entered serial number in your "unified" student record table and use it to link to a matching field in each such table that you set up. Note that with "allow creation of records via this relationship", you can link the detail record simply by typing data in a blank portal row or blank set of fields that link to the detail record. Filemaker will create a new record in the related table and copy the primary key's value into the match field of the related table for you.
Like • Show 0 Likes0
Actions
RickWhitelaw Jul 30, 2010 9:08 PM
Mark Correct
Correct Answer
"I know there is something about using TO's here, but for the life of me I can't figure out how splitting it up into TOs really helps, or even how to do that.."

You don't split data into TOs (as I'm sure you know). Generally you create a TO to avail yourself of a different relationship to another table or TO. Two different relationships between two TOs will return different results. If one TO is related to another simply by PK (serial) the relationship will return only those related by the match field. If the relationship is something like Date > or = Date in the other TO plus another match, the relationship will return a (likely) different data set. In the solution I use, several Table Occurrences of the same source table are often used to establish different relationships that allow the retrieval of various data sets.

RW
Like • Show 0 Likes0
Actions

Many tables vs. just two tables dilemma

Title

Post

Outcomes

Related Content

Recommended Content