K
kspace.image
Hey, I'm planning a small database to keep track of scans in my MRI
lab. We have a set protocol of scans that are given (T1, T2, etc.)
but the order, and whether or not they were given to every subject,
changes.
I'm definitely going to have a table for the scans themselves (i.e.
"pkScanType", "ScanTypeDescription"), and a table for each scan
session (i.e. "pkSession", "fkSubject", "ScanDate",) but I'm trying to
figure out where I should store the information about the individual
scans themselves (like the T1 that only completed partially on this
date). In a previous database I created I put all of the scans
themselves linked back to the session in which they occurred (i.e.
"pkScanSeries", "fkScanSession", "fkScanType", "Order", "Comment") so
that there were several records for each scan date. I think this is
the most normalized way of storing the info, but it has become a pain
to query for whether or not a particular subject received a particular
scan; I've designed some work-arounds based on queries of "people who
got a T1" or "people who got a T2", but it's not exactly the simplest
method.
I'm considering putting all of the scans in one main scan table, so
that there would be only be one record per scan, with fields like "T1
Order", "T1ScanComment", "T2Scan", "T2ScanComment", etc. as foreign
keys to a table listing the scans we have (i.e. fields like
"pk","ScanDescription"). I know this is lazy DB admin, but it would
allow easy querying (i.e. WHERE Not IsNull(T1Order) ) and wouldn't
stretch out the schema too far.
I know there will always be at least 10 or so scans, so that's really
only 20 fields, ScanOrder + Comment. My concerns are a lack of
normalization and wasted space (i.e. if I have one session where 20
scans were entered and I make the corresponding fields, all the other
sessions where fewer scans were run are forced to be empty). Would
this create headaches down the road, or is that not really a concern?
What would you do? Thanks for your help,
Erik
lab. We have a set protocol of scans that are given (T1, T2, etc.)
but the order, and whether or not they were given to every subject,
changes.
I'm definitely going to have a table for the scans themselves (i.e.
"pkScanType", "ScanTypeDescription"), and a table for each scan
session (i.e. "pkSession", "fkSubject", "ScanDate",) but I'm trying to
figure out where I should store the information about the individual
scans themselves (like the T1 that only completed partially on this
date). In a previous database I created I put all of the scans
themselves linked back to the session in which they occurred (i.e.
"pkScanSeries", "fkScanSession", "fkScanType", "Order", "Comment") so
that there were several records for each scan date. I think this is
the most normalized way of storing the info, but it has become a pain
to query for whether or not a particular subject received a particular
scan; I've designed some work-arounds based on queries of "people who
got a T1" or "people who got a T2", but it's not exactly the simplest
method.
I'm considering putting all of the scans in one main scan table, so
that there would be only be one record per scan, with fields like "T1
Order", "T1ScanComment", "T2Scan", "T2ScanComment", etc. as foreign
keys to a table listing the scans we have (i.e. fields like
"pk","ScanDescription"). I know this is lazy DB admin, but it would
allow easy querying (i.e. WHERE Not IsNull(T1Order) ) and wouldn't
stretch out the schema too far.
I know there will always be at least 10 or so scans, so that's really
only 20 fields, ScanOrder + Comment. My concerns are a lack of
normalization and wasted space (i.e. if I have one session where 20
scans were entered and I make the corresponding fields, all the other
sessions where fewer scans were run are forced to be empty). Would
this create headaches down the road, or is that not really a concern?
What would you do? Thanks for your help,
Erik