[ome-devel] Database Issues: FK, UNIQUE, and such

[Ilya Goldberg]

On May 31, 2005, at 3:25 PM, Joshua Moore wrote:
>
> Certainly don't want any loose gooses (regardless of the connotation of
> "loose"), but there's also something to be said for guaranteeing data
> integrity at the db level. Couldn't the FKs _sensibly_ be represented 
> at the
> data model level?

That's the plan.  I *almost* have it working this way.
Basically, any time you declare a reference in an ST or a has_many in a 
class, an FK constraint will be generated for that table.
That's sensible, no?

>>

>>> III. One-to-One
>
>> We don't currently support explicit "has-one" relationships using
>> references.  So for now at least, an image can in fact have multiple
> dimensions.
>
> Back to data integrity, does it make sense for an image to have 
> multiple
> dimensions, and how to I deal with that programmatically?
>
>   if (dimensions.length > 1) throw new OopsException()

We could deal with this at the API level via ImageManager->dimensions().
Continuing with data integrity, we have this other thing going on that 
attributes are immutable (not currently enforced by the DB BTW, but by 
mutual agreement about the datamodel).  That means that if you place a 
unique constraint on dimensions.image, you will never be able to edit 
those dimensions and they will be that way forever.  Since these are 
persistent objects, forever is a very long time.  Surely, if you were 
to edit the dimensions, you would want to keep a record of what the 
original dimensions were and who changed them and when.  If you allow 
multiple dimensions, you make the record editable (and auditable), but 
are stuck with the possibility of having multiple entries.  If you 
enforce uniqueness, then you are stuck with an uneditable record.
We've thrown this conundrum at the clients basically by saying that the 
client has to know which of these entries it cares about - the 
"original", the "latest", something in between, "my" dimensions, etc.  
Is there a general answer to this?  I'm not sure that the right answer 
is to say that these records are uneditable.  If we decide to make them 
uneditable, that would actually be pretty straight forward to implement 
- the exception being thrown by the DB when an attempt is made to make 
a new entry.

>>>  * Should the maps have a UNIQUE constraint on both fields.
>>
>> Explicit many-to-many relationships (maps) are not supported in STs.
>> This could in-fact be done transparently by saying that an ST
>> consisting of only references gets a UNIQUE constraint on all its
>> fields.
>
>
> Sounds awfully implementation-ly. What about whether they /should/ have
> them? And again, how do I take care of it programmatically if there's 
> the
> possibility that there will be multiple maps (to get all 
> implementation-ly
> myself)?

I would tend to agree - the solution isn't to force unique map entries, 
again because of the audit trail.  Don't we want to know that a new 
association was formed at some later time and keep that separate from 
the original?

All these audit trail issues are not exactly moot, though they cause us 
much consternation at the edges.  If you have a few days to spare, you 
can look at FDA 21 CFR Part 11, which pharmaceutical companies are 
going to need to comply with someday soon.  One of the key requirements 
there is an audit trail for electronic data, and OME is one of the few 
(possibly only) image informatics management systems that implements 
this.

>>>  * Projects and datasets are many-many. CGs and Cats are 1-Many.
>>> Screens and
>>> plates as well? What is/will be the rule-of-thumb for these (and
>>> future)
>>> containers?
>>
>> What did you have in mind?
>
> What I had in mind was, roughly, that there seems to be such a parallel
> drawn between PDI and CGCI in many (public) arena. If we ignore that 
> PDI is
> somehow class based and CGCI is ST based, one might expect that they 
> obey
> the same hierarchy constructs. Certainly not a must, it's just what 
> I've
> come to expect from how we've talked about the various hierarchies.

I agree that eventually we will indeed be able to ignore the ever 
diminishing differences between class-based and ST-based objects.  They 
do obey the same hierarchy constructs - either a strict hierarchy or a 
more general graph.  We want both possibilities for describing context 
- don't we?  Maybe you're asking something much more specific - do we 
use different styles to traverse these contexts when they are 
class-based vs. when they are ST based?  There are some subtle 
differences, but they should be quite similar.  Is there some specific 
difference you were referring to?

-I

>
> -J
>
> -- 
> Weitersagen: GMX DSL-Flatrates mit Tempo-Garantie!
> Ab 4,99 Euro/Monat: http://www.gmx.net/de/go/dsl
>