I am a graduate student working on breast cancer in a veterinarian facility. I am designing a database to keep track of the health of my mouse colony.
Each day, animal care staff verify each cage for health problems. If they find something suspicious (e.g. tumour, skin ulceration), they file a health report for the veterinarian to check. Each report can have multiple observations.
Then, the vet decides whether or not it is a real case. If it is real, the vet will create a clinical case number with open/close dates. If not, they won't open a case. However, I want to keep track of these "false positives" for further study (at this point, a primary key can be automatically created).
Here is my tentative database design:
Please correct me if I am wrong, but I have created two transition tables (red) to respect the first nominal form for the observations (i.e. only one value per entry)?
Finally, I have not seen any database design like mine where the tables are connected into a circle, so I am worried that I have made an error.
I would greatly appreciate the community's feedback. Thank you!
Best Answer
So, just to clarify:
You want 1 Report to have Many Observations. The Observations then become either a Case or NoCase. Now, each case/nocase is linked by the ObservationID and the ReportID (or the Observation itself belongs to a ReportID), right?
It wouldn't be a circle: Report->Observations->Case/NoCase (I can see the Case and NoCase entities as one specialization).