Many tables vs. just two tables dilemma
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,