Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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}
share|improve this answer
    
That seems so simple in retrospect; it will absolutely do what I need it to. (The metadata is pretty unique, so having it split into a different table won't work for me, but thanks for the suggestion). I'm retrofitting two existing systems into place and must have been getting lost in the process of going from one to the other instead of thinking about what it should really look like. Thanks for your perspective! –  Erik Kastman Jun 17 '11 at 16:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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