4

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:

enter image description here

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!

3
  • The references are one-way so when you add the arrows, this is not really a circle.
    – jkavalik
    Feb 6, 2016 at 17:31
  • 3
    I (geneticist in a previous life) would consider (maybe) Observation as my primary table - staff find "something" (an observation) which may or may not warrant further consideration. Possibly an observation may become a Case (another table). As you say, it would be a good idea to track everything. Also, bear in mind that things which were observations may, in future, become cases. My advice: keep track of everything, you never know what will be important later. Link observations to cases.
    – Vérace
    Feb 6, 2016 at 18:28
  • @Vérace - you should add that as an answer.
    – Hannah Vernon
    Feb 6, 2016 at 20:04

2 Answers 2

1

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).

3
  • Thank you for your answer! So, it's ok if Case and NoCases use the same Observation table?
    – Johnathan
    Feb 6, 2016 at 19:18
  • You're welcome. Yes, you can give a foreign key ObservationID on Case and NoCase, and these tables could be of cardinality 0,1. You can also use specialization, as I stated in the answer, I suggest you to read a bit about it. They're good for subtypes, but I believe it'd be good for your schema. You have other options too, just look at the design and create new ones till you find the most effective for your project. They won't be cyclic tho, because there won't be a loop between Cases/NoCases and Reports.
    – Edu C.
    Feb 6, 2016 at 22:08
  • Also, read the comments on your question, they could help you to! :)
    – Edu C.
    Feb 6, 2016 at 22:09
0

First of all, kudos on your effort at designing a DB for this problem! It's great to find people from different backgrounds using software to solve their problems.

Now, for the meat. For starters, generally for database (or any kind really) design problems, there can be many possible solutions. Depending on the requirements and constraints for a particular problem, those solutions may become acceptable or not. In this case, while it may work, I would suggest improving your design by reducing the number of tables and relationships between them, and therefore more cleanly and succintly representing your real world situation.

Something like this is what I would set up: Possible solution to DB design problem

  1. A HealthReport record is created whenever something wrong is found. I would suggest having a cage ID instead of a room ID, if possible (this, of course, depends on what data is available).
  2. Observations are made for each HealthReport. These are basically just descriptions of issues found with mice in the report, but you may extend it if you want to. Depending on how specific you want (or can) make your data, some possible extensions would be: mouse gender, weight, age, a type of observation/injury, number of individuals... the list can go on and on. Again, this depends on what data is available for you and what you wish to include. Also, keep in mind a possible extension mechanism would be to add a ExtendedObservations table that references Observations, in which you may include additional data. That would be more complicated and IMHO, not necessary for this particular application... but it's a possibility.
  3. A Case is filed for each of the HealthReports. Here, again, you may choose to represent the "no case was filed for a report" event by not adding a record to the Case table (which would make the IsReal column redundant and therefore warrant its deletion), or, as I have, by creating a Case for each HealthReport and have it specify whether a vet determined it was valid or not. Note that the DateClosed allows for NULLs in case you want to add the record of an open case first and close it at some later point in time. If you do not want to allow this, I would suggest not allowing NULL values to be inserted into the table to maintain better data integrity. I would strongly suggestnot to allow NULLs if you can help it, so your data is as dense as possible (then you won't have to worry if a NULL is there because a Case is still open or because someone forgot to close it).

Notice HealthReports is the principal table here, around which the data is structured. This seems to me a more natural way to structure it based on the workflow you have described.

I hope this helps!

2
  • Thank you very much for your input! I was wondering: would it be a good idea to use the observation table as the primary table as suggested in the comment section?
    – Johnathan
    Feb 7, 2016 at 23:40
  • Well, this really depends on your case. From what I understand, I think it's probably not a good idea. Can you have observations exist in the system by themselves (i.e. record an observation that does not belong as part of a Health Report? If that is the case, then I'd suggest editing your question so the answers you get are closer to what you actually need :). Feb 8, 2016 at 1:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.