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