How to link Similar-but-distinct Models of Medical Imaging Data

database-design

I'm tracking a set of medical images in a logbook-type application, and am having a problem with how to model my records. The problem is that for every group of images collected, there are two similar, but distinct ways of recording data about a given scan: the first being done by a study coordinator at the scanner, writing description, order and comments about each image, and the second by scanning the actual images' metadata a day or so later when they are transferred to us and have become available. Both models (the image metadata and the hand-written log) are about the same thing (each image), but are entered at different times.

My question is: How should I connect the two models? Since they would both be the same order belonging to the same scan, I can match them up programmatically, but that seems like it would be a lot of overhead, and simultaneously not very flexible. I could connect them with a join table or a foreign key for one or the other, but that might lead to some internal inconsistencies. I can't model them as exactly the same model, because there are some extra tasks that behave like images but aren't images, in the hand-written log, and they don't have images or metadata.

Is it better just to be rigid and link them programmatically, without any keys relating them? The link would depend on the data being correct (but I guess that's always the case).

Best Answer

Since you said that there are always 2 types of data I would do it like this

This might use the wrong terms since I don't know what exactly is available, but I think you will get the idea.

image (the data that is common to both scanes/images)
---------------------------
    image_id
    date


coordinator (the one that is inserted by hand)
---------------------
    image_id (PrimaryKey and ForeignKey)
    comment
    description
    order
    [...]

metadata (that you get programmatically)
----------------------
   image_id (PrimaryKey and ForeignKey)
   {whatever data that is}

This way, you have both images connected but have the freedom to model the details differently.

Additionally, you could move the metadata into a seperate table if the data is similar. This might be useful for later comparisson/collection/...

metadata (that you get programmatically)
----------------------
    image_id (PrimaryKey and ForeignKey)
    metadata_id


metadata_description
-------------------------
    metadata_id
    description
    name
    {whatever the metadata is}
Related Question