Hans Wenzel:

Hi all 

here are my comments to the proposed schema. 
there are 2 tables (histogram and chart) and none of them is referenced in the schema. 
Why not combine them to the proposed datatable below (in case of charts the arrays bin_min and bin_max would set to null.) 

Currently 

CREATE TABLE HISTOGRAM( 
       ID SERIAL PRIMARY KEY, 
       title VARCHAR(500), 
       axis_title VARCHAR(100)[], 
       value_title VARCHAR(100)[], 
       nbins INT[], 
       bin_min REAL[], 
       bin_max REAL[], 
       value REAL[], 
       value_err_sys_plus REAL[], 
       value_err_sys_minus REAL[], 
       value_err_stat_plus REAL[], 
       value_err_stat_minus REAL[] 
); 

CREATE TABLE CHART( 
       ID SERIAL PRIMARY KEY, 
       title VARCHAR(500), 
       npoints INT, 
       x_axis_title VARCHAR(100)[], 
       x_val REAL[], 
       x_err_stat_plus REAL[], 
       x_err_stat_minus REAL[], 
       x_err_sys_plus REAL[], 
       x_err_sys_minus REAL[], 
       bin_min REAL[], 
       bin_max REAL[] 
); 

Proposal: 

CREATE TABLE DATATYPES( 
DTYPE INTEGER PRIMARY KEY, 
DESCRIPTION CHARACTER VARYING(50) UNIQUE 
); 

CREATE TABLE DATATABLE( 
       DTID SERIAL PRIMARY KEY, 
       DTYPE       INTEGER  REFERENCES DATATYPES(DTYPE), 
       title       CHARACTER VARYING(100), 
       npoints     INTEGER, 
       axis_title  CHARACTER VARYING(100)[], 
       val REAL[], 
       err_stat_plus REAL[], 
       err_stat_minus REAL[], 
       err_sys_plus REAL[], 
       err_sys_minus REAL[], 
       bin_min REAL[], 
       bin_max REAL[] 
); 

I would rename what you call datatable which is the combination of the experimentresult and 
testresult to  ResultMetaData since it better represents the data stored there. 
I removed the link to MCTOOL since I believe it belongs to the test table describing the test I think that a test is uniquly associated to either 
geant4, experiment or genie. 
As mentioned above the current table doesn't contain a link to the actual data which I added refering to the proposed datatable. 
I also propose we explicitely include a timestamp which defaults the value of modtime 
to be the time at which the row was inserted. All references are references to the id see below why I prefer that instead of referencing names. 

so your DATATABLE would become: 

CREATE TABLE RESULTMETADATA ( 
   RMID         SERIAL  PRIMARY KEY, 
   TID          INTEGER REFERENCES TEST(TESTID),                         -- REFERENCES THE TEST THAT TEST RESULT IS ASSOCIATED WITH 
   VERSION      INTEGER REFERENCES VERSION(VERID),                       -- REFERENCES TAG OF GEANT 4 VERSION USED 
   MODEL        INTEGER REFERENCES MODEL(MODID),                         -- REFERENCES TABLE WITH THE MODEL OR PHYSICSLIST USED 
   BEAM         INTEGER REFERENCES BEAM(BID),                            -- REFERENCES PARTICLE TABLE (PDGID,NAME, ...) 
   TARGET       INTEGER REFERENCES MATERIAL(MID),                        -- REFERENCES TABLE defining materials 
   OBSERVABLE   INTEGER REFERENCES OBSERVABLE(OID),                      -- REFERENCES TABLE defininG OBSERVABLES (CROSS SECTION, MOMENTUM OF OUTGOING PARTICLES..) 
   SECONDARY    INTEGER REFERENCES PARTICLE(PDGID),                      -- REFERENCES PARTICLE TABLE (PDGID,NAME, ...) 
   REACTION     INTEGER REFERENCES REACTION(RID),                        -- REFERENCES REACTION TABLE (SCATTERING, PARTICLE PRODUCTION, CAPTURE, DECAY....) 
   DATATABLEID  INTEGER REFERENCES DATATABLE(DTID),                      -- REFERENCES DATATABLE 
   SCORE        INTEGER REFERENCES SCORES(SCOREID),                      -- TEST SCORE AND TYPE 
   ACCESS       INTEGER REFERENCES ACCESS(ACCESSID),                     -- controls who has access to the test result 
   KEYWORDS     CHARACTER VARYING(50)[],                                 -- keywords associated with the testresult e.g. angle of outgoing particle 
   MODTIME      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP       -- make the default value of modtime be the time at which the row is inserted 
); 

Minor comments: 
The particle table needs an primary key different from the pdgid. 
May be also the reference/paper table needs an additional ID different from the inspireid in case we are referencing e.g. an experimental 
web site. 

With regards to your questions: 

1) I do not really understand a gain of usage different primary keys names (id) 
and "names" in each table...why do we need them have different? 

DTID        SERIAL PRIMARY KEY 
VERID       SERIAL PRIMARY KEY 
MODID     SERIAL PRIMARY KEY, 
RID SERIAL PRIMARY KEY 

And same for names - DTNAME, VERNAME, MODNAME, RNAME... 
I would propose to use ID and NAME. 

*** This was a choice to make coding easier (especially for summerstudents). 
When I called it id/name everywhere it could be confusing what id we were 
actually refering to but if we call it e.g. wgid it's right away clear that 
we are that we are refering to a working group id.  Of course this is redundant because we are 
refering to an ID in different tables/API Classes. I think this just makes it easier but I 
don't feel strongly about it. 
While the names that we store in the dictionary table is unique I prefer to refence an unique integer id avoiding string comparisons. 
Also if we have a typo in the name we can change it without having to change any of the tables referencing the name. 

2) I see that in your original schema you have a table called "SCORES". I think we should 
NOT have a reference from DATATABLE to table SCORES but vice-versa - 
to have SCORES table referencing to DATATABLE. In this case you can change "scores" not 
touching "central" tables". 

***Don't understand the argument we refer to the score via its id nothing prevents you from changing the scorevalue 
and scoretype in the score table without touching the table with the meta data which references the score. On the 
other hand looping over scores to find the metadata table we are refering to is inefficient. 

-- 
Dr. Hans Wenzel 

Physics and Detector Simulation 
Fermi National Accelerator Laboratory 
P.O. Box 500, MS 223 
Batavia, Illinois 60510 
USA 

630 840 6034 office 
331 250 9342  mobile 
home.fnal.gov/~wenzel 
wenzel@fnal.gov