Re: [GUSDEV] An important schema question (to me, anyways)

[Deborah P. <pi...@pc...>]

There is a case here at Cbil, where we need to have the constraint in 
place. In that case dots.naentry is used as a book-keeping tool to keep 
track of modifications of Genbank entries. I think at one time the 
constraint did not apply to dots.aaentry.source_id (I can't swear on 
that though). That may have changed for the sake of consistency.





Josef Jurek wrote:

>Well, if people do not feel that source_id requires
>an unique constraint in tables AAEntry/NAEntry,
>I propose that these constraints be removed.  I can
>of course just remove them in our local gus installations,
>though I would prefer that our local development
>of tools be compatible with canonical GUS.
>
>
>Another option would be to just include the fields:
>
>      CREATED_DATE                 DATE
>      CREATED_REL_VER              NUMBER(5)
>      SEQ_DATE                     DATE
>      SEQ_REL_VER                  NUMBER(5)
>      ANNOT_DATE                   DATE
>      ANNOT_REL_VER                NUMBER(5)
>
>into AASequenceImp/NASequenceImp and eliminate
>AAEntry/NAEntry.  I can see no reason that these
>need to be separate tables (though please, someone
>correct me if there is a reason) and this would
>be an instance of simplifying the schema without
>loosing functionality.
>
>Ideas, anybody?
>
>Oh, and I still interested if anyone knows why a unique
>constraint is presently applied to source_id of
>AAEntry/NAEntry.
>
>Thanks;  Josef
>
>
>
>Daphne Preuss Laboratory
>Molecular Genetics and Cell Biology
>The University of Chicago
>ju...@cs...
>
>voice: (773) 834-3985
>fax:   (773) 702-6648
>
>
>
>
>Josef Jurek <ju...@cs...> writes:
>  
>
>>In looking for a place to put ncbi/swissprot entry dates,
>>I see that the tables DoTS.AAEntry/DoTS.NAEntry were
>>probably made for this purpose.  For example, here are
>>the dates from a typical swissprot record:
>>
>>        16-OCT-2001 (Rel. 40, Created)
>>        16-OCT-2001 (Rel. 40, Last sequence update)
>>        24-JAN-2006 (Rel. 49, Last annotation update)
>>
>>and here is what the table looks like:
>>
>>   DoTS.AAEntry;
>>    Name                Null?    Type
>>    ------------------- -------- ----------------
>>    AA_ENTRY_ID         NOT NULL NUMBER(10)
>>    AA_SEQUENCE_ID      NOT NULL NUMBER(10)
>>    SOURCE_ID           NOT NULL VARCHAR2(15)
>>    STATUS                       VARCHAR2(12)
>>    CREATED_DATE                 DATE
>>    CREATED_REL_VER              NUMBER(5)
>>    SEQ_DATE                     DATE
>>    SEQ_REL_VER                  NUMBER(5)
>>    ANNOT_DATE                   DATE
>>    ANNOT_REL_VER                NUMBER(5)
>>
>>It looks perfect.
>>
>>
>>HOWEVER, not only is source_id "NOT NULL", it has a unique constraint
>>on it.
>>
>>     Every source_id field in this table must be unique.
>>
>>And though the column source_id is common in tables throughout
>>most GUS schemas,
>>
>>    DoTS.AAEntry/DoTS.NAEntry are the only two tables that have
>>    this unique constraint on source_id.
>>
>>
>>Jeez; what is going on here?  What is the intended use for
>>this field?  I suppose I can put an accession number here,
>>but I was already putting accession numbers in
>>DoTS.ExternalAASequence.  Why would I or anyone want
>>to duplicate this?
>>
>>Please, would someone explain what the intention is here.
>>
>>Thank you for your help;
>>
>>Josef
>>
>>
>>
>>
>>    
>>
>
>
>
>
>
>-------------------------------------------------------
>This SF.Net email is sponsored by the JBoss Inc.  Get Certified Today
>Register for a JBoss Training Course.  Free Certification Exam
>for All Training Attendees Through End of 2005. For more info visit:
>http://ads.osdn.com/?ad_id=7628&alloc_id=16845&op=click
>_______________________________________________
>Gusdev-gusdev mailing list
>Gus...@li...
>https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>  
>