1 Reply Latest reply on Nov 24, 2008 9:46 AM by TSGal

    Newbie help in structuring some data



      Newbie help in structuring some data


      Hi All

      Looking for a little newbie help here. I've been learning about relational databasing and I think I finally have a good grasp on how to structure a database and use relationships etc. but I'm not sure how to proceed with modeling this particular set of data, hopefully someone can provide some insight. Here's the scenario in brief:


      We're cataloging  videos of medical therapy/diagnosis. We also use the video to derive edited clips to demonstrate (in lectures) exaples of different diagnosis. Using best practices, I've boroken the data down to three tables: Client (who the video is of); Raw footage; and edited clips. Each table has a relationship via the ClientID and via the raw footageID. ie: Raw footage A is of Client A; Raw footage B is also of client A. Clip A and B is of Client A and comes from Raw footage A; Client A can have multiple clips associated - clip A, B, C as well as multiple raw footages (like from different therapy visits) etc etc. I've got that all straight, but here's where I'm a little stuck:


      There's a diagnosis that goes with each - we'll call it diagnosis1, diagnosis2, etc. Within each diagnosis, there's a sub-diagnosis - we'll call it sub1, sub2, etc. The diagnosis can relate to the patient, the raw footage and/or the clips. For example, Client A's diagnosis might be diagnosis1, sub2. His raw footage might demonstrate an example of diagnosis2, sub2, and a clip might demonstrate an example of diagnosis3, sub2 etc etc as well as multiple diagnosis/sub diagnosis. Just about any combination is possible. So different (and multiple) diagnosis and sub diagnosis can apply to each record in each table.


      So the diagnosis is an independent attribute of each entity. It seems redundant to have a seperate data field for the diagnosis/sub for each record in each table. The question is, in best practice, how can I categorize this data without redundancy? It makes sense to me to break the diagnosis/sub out into a seperate table, but I can't quite wrap my brain around how to create the relationships. These would be one-to-many relationships - each client can have more than one diagnosis/sub, and the same for the other entities (raw footage and video clips).


      Also, I don't quite get yet how to structure data that is multi-layered or nested - diagnosis1, sub1 etc. Is this represented as two seperate fields ie: a field for diagnosis with a value list of 1,2,3 and another field for sub-diagnosis with a value list of 1,2,3? I think that's the best practice way to do it, but I'm not sure. Note: there can be multiple diagnosis as well - clientA can have diagnosis1 and diagnosis2. Multiple entries in one field seperated by carriage returns? aargh!


      Can you tell I'm a little overwhelmed? If any of this makes any sense, please help! :)

      Thanks in advance! 

        • 1. Re: Newbie help in structuring some data



          Thank you for your post.


          It appears you answered your own question.  That is, you will need another table for diagnosis since it can be linked to either a Client, Raw Footage or Clip.  Therefore, your diagnosis table needs fields to link to all three tables.  Therefore, you could have the following fields in the Diagnosis table:


          Diagnosis Notes

          Sub-Diagnosis Notes

          Client ID (linked to Client ID in Client table)

          Raw Footage ID (linked to Raw Footage ID in Raw Footage table)

          Edited Clip ID (linked to Edited Clip ID in Edited Clip table)


          If you have two diagnosis for the same client, then there would be two records in this table with a Client ID, and the Raw Footage ID and Edited Clip ID would be empty.


          I'm hoping this gives you some ideas.  If you need clarification for anything I covered, please let me know.



          FileMaker, Inc.