Re: [Gusdev-gusdev] Relation between tables

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Okay, first an overview of the table and after that the sql code
Following table I need (* - to implement)
EnzymeClass
EnzymeClassAttribute
Pathway  - I do not know if the dots::pathway does what I want :-)
compound *
reaction *
Motif/Prosite
Disease *
Classment for Functions and Proteins *

Relations:
1. CompoundRelations * - will hold the relation to reaction, enzyme,
2. EnzymeRelations * - will hold the relation of the enzyms to reaction, 
Motif, Diesease
ECpathway - would be obsolete

The namespace should be sres.

Maybe I could put 1 and 2 together, but... anyhow, all data are related. 
(All information comes from kegg)

Here the tables:

the question of the two following table is, if it would be better to do 
a table compoundattribute...

CREATE TABLE SRes.compound  (
    compound_id          number(12)                                    
NOT NULL,
    ENTRY            VARCHAR2(90)                                      
NOT NULL,
    NAME            VARCHAR2(90)                                      
NOT NULL,
    FORMULA            VARCHAR2(90)                                      
NULL,
    DBLINKS            
VARCHAR2(120)                                      NULL,
    MODIFICATION_DATE                  
DATE                                          NOT NULL,
    USER_READ                          NUMBER(1) 
...
CREATE TABLE SRes.reaction  (
    reaction_id          number(5)                                    
NOT NULL,
    ENTRY            VARCHAR2(90)                                      
NOT NULL,
    NAME            VARCHAR2(90)                                      NULL,
    DEFINITION            
VARCHAR2(90)                                      NULL,
    EQUATION            
VARCHAR2(120)                                      NULL,
    COMMENTS            
VARCHAR2(120)                                      NULL,
    MODIFICATION_DATE                  
DATE                                          NOT NULL,
    USER_READ                          NUMBER(1)
...
 Motif and Disease not sure, I think I will put the Attributes like in 
the EnzymeClassattribute - table

EnzymeRelations and CompoundRelations will be the same.
CREATE TABLE SRes.EnzymeRelations  (
    Enzyme_Relations_id         
 number(12)                                    NOT NULL,
    enzyme_class_id              number(12)                NOT NULL,
    foreign_id            number(12)                     NOT NULL,
    TYPE                Varchar2(20)            NOT NULL,
    MODIFICATION_DATE                  
DATE                                          NOT NULL,

So that's my idea.
Thomas

Steve Fischer wrote:

> Thomas-
>
> when you have a first draft of your ideas, send us the table or 
> tables, including the attributes and data types.   that way we will be 
> able to give you the best advice.
>
> steve
>
> Thomas Otto wrote:
>
>> I think I will all the relation in one table... sort of 
>> EnzymeRelations...
>>
>> Thomas
>> Thomas Otto wrote:
>>
>>> Okay, an example:
>>> To every enzyme are several reaction associated. i.e. Enzymea, has 
>>> reaction1, ..., reaction n.
>>> (1) I could be to save the name of the reaction in the table 
>>> EnzymeClassAttribute
>>> (2) would be, to save nothing of this in the EnzymeClassAttribute, 
>>> but in a table (to create) EnzymeReactionRelation.
>>>
>>> This will mean a lot of new table, because just for the enzymes 
>>> there are pathways, reaction, compound, substrate, product...
>>>
>>> But indeed, I also prefer #2, who is less redundend.
>>>
>>> Thanks,
>>> Thomas
>>>
>>> Angel Pizarro wrote:
>>>
>>>> In the absence of an example of data a definite schema, definitely 
>>>> #2 as the data model of choice. You can always implement 
>>>> materialized view to speed-up/ease  queries.
>>>>
>>>> Cheers
>>>> Angel
>>>>
>>>> Thomas Otto wrote:
>>>>
>>>>> That's what I was thinking about. So I want to know, which 
>>>>> representation of datas you prefer. (1 or 2, it is more a general 
>>>>> question).
>>>>>
>>>>> Thomas
>>>>>
>>>>>
>>>>> Angel Pizarro wrote:
>>>>>
>>>>>> OK, I don't see where you are getting EnzymCompoundRelation & 
>>>>>> CompoundClassAttribute from. Our current version of GUS does not 
>>>>>> have these tables. The SRes.EnzymeClass table is also looking 
>>>>>> very wacky to me. Propose a nice structure for this and we can 
>>>>>> put it in the next release of GUS.
>>>>>>
>>>>>> Angel
>>>>>>
>>>>>> Thomas Otto wrote:
>>>>>>
>>>>>>> Hello,
>>>>>>>
>>>>>>> I am uploading some kegg data in the GUS system...
>>>>>>>
>>>>>>> So I looked which tables are existing, and which we are needing 
>>>>>>> i.e. for compound, reactions. Now I am not sure how is the 
>>>>>>> convention to relate the data.
>>>>>>>
>>>>>>> Example:
>>>>>>> The Enzymes are related to the compounds.
>>>>>>> (1) In EzymeClassAttribute I write the names of the compound 
>>>>>>> related to each enzyme. In CompoundClassAttribute I write also 
>>>>>>> all EC-numbers, related to it.
>>>>>>> (2) Other possibilty would be to utilize a thrid table 
>>>>>>> EnzymeCompoundRelation, where I put the primkeys of the relations.
>>>>>>>
>>>>>>> (1) would be faster, easier to query, but redundend.
>>>>>>> (2) Cleaner
>>>>>>>
>>>>>>> What are you thinking I should use?
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Thomas
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> -------------------------------------------------------
>>>>>>> This SF.Net email is sponsored by: IBM Linux Tutorials
>>>>>>> Free Linux tutorial presented by Daniel Robbins, President and 
>>>>>>> CEO of
>>>>>>> GenToo technologies. Learn everything from fundamentals to system
>>>>>>> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
>>>>>>> _______________________________________________
>>>>>>> Gusdev-gusdev mailing list
>>>>>>> Gus...@li...
>>>>>>> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> -------------------------------------------------------
>>>> This SF.Net email is sponsored by: IBM Linux Tutorials
>>>> Free Linux tutorial presented by Daniel Robbins, President and CEO of
>>>> GenToo technologies. Learn everything from fundamentals to system
>>>> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
>>>> _______________________________________________
>>>> Gusdev-gusdev mailing list
>>>> Gus...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>>>>
>>>
>>>
>>>
>>>
>>> -------------------------------------------------------
>>> This SF.Net email is sponsored by: IBM Linux Tutorials
>>> Free Linux tutorial presented by Daniel Robbins, President and CEO of
>>> GenToo technologies. Learn everything from fundamentals to system
>>> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
>>> _______________________________________________
>>> Gusdev-gusdev mailing list
>>> Gus...@li...
>>> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>>>
>>
>>
>>
>>
>> -------------------------------------------------------
>> This SF.Net email is sponsored by: IBM Linux Tutorials
>> Free Linux tutorial presented by Daniel Robbins, President and CEO of
>> GenToo technologies. Learn everything from fundamentals to system
>> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
>> _______________________________________________
>> Gusdev-gusdev mailing list
>> Gus...@li...
>> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>