Newbie help in structuring some data
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!