gusdev-gusdev

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

[Josef J. <ju...@cs...>]

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





Josef Jurek, Ph.D.

Daphne Preuss Laboratory
Molecular Genetics and Cell Biology
The University of Chicago
ju...@cs...

voice: (773) 834-3985
fax:   (773) 702-6648

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

[Josef J. <ju...@cs...>]

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

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

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

[Josef J. <ju...@cs...>]

Deborah Pinney <pi...@pc...> writes:
>
> 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.

This should be the purpose of the *_DATE and *_REL_VER fields,
to keep track of the modification of genbank entries.
Why would you need anything else?

This unique constraint sounds like it was implemented for
idiosyncratic reasons by someone at CBIL.  I'll bet this field
will continue to completely serve your needs if the unique
constraint is removed.

That the source_id field is provided in many tables to give
users at their respective sites the ability to do local
bookkeeping is great.  Putting a unique constraint
on this field for a perceived local bookkeeping need
is just a stumbling block that adds a bit of difficulty
to the GUS community.


So, shall this unique constraint on source_id of
AAEntry/NAEntry be eliminated?  (We don't have to
decide today).

Thanks for the response; Josef



> 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
[...]

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

[Chris S. <sto...@pc...>]

Hi Josef,
The unique constraint on source_id of AAEntry/NAEntry will be  
eliminated in  the next release. To help me remember, please enter a  
bug request. Thanks for bringing this up.

Cheers,
Chris

On Nov 16, 2005, at 3:53 PM, Josef Jurek wrote:

>
> Deborah Pinney <pi...@pc...> writes:
>>
>> 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.
>
> This should be the purpose of the *_DATE and *_REL_VER fields,
> to keep track of the modification of genbank entries.
> Why would you need anything else?
>
> This unique constraint sounds like it was implemented for
> idiosyncratic reasons by someone at CBIL.  I'll bet this field
> will continue to completely serve your needs if the unique
> constraint is removed.
>
> That the source_id field is provided in many tables to give
> users at their respective sites the ability to do local
> bookkeeping is great.  Putting a unique constraint
> on this field for a perceived local bookkeeping need
> is just a stumbling block that adds a bit of difficulty
> to the GUS community.
>
>
> So, shall this unique constraint on source_id of
> AAEntry/NAEntry be eliminated?  (We don't have to
> decide today).
>
> Thanks for the response; Josef
>
>
>
>> 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
> [...]
>
>
>
>
> -------------------------------------------------------
> 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

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

[Josef J. <ju...@cs...>]

Chris Stoeckert <sto...@pc...> writes:
> 
> The unique constraint on source_id of AAEntry/NAEntry will be  
> eliminated in  the next release. To help me remember, please enter a  
> bug request.

Will do;

> Thanks for bringing this up.

And thank you for taking a look;


Also, something you might want to consider is putting the
fields of AAEntry/NAEntry into ExternalAASequence/ExternalNASequence
and then eliminating AAEntry/NAEntry.

As the ExternalAASequence/ExternalNASequence views are
intended to hold information on public sequences such
as those from genbank/swissprot records, then there
is no reason to keep the dates of these records
in a separate table.

I was actually struggling with finding an elegant way of
putting swissprot sequence dates into ExternalAASequence
until I stumbled upon AAEntry.

I can see no reason why the dates of these records
should be in a separate table, and actually simplifying
the schema without loosing any functionality would
be a big plus.

Thanks,

Josef