Dmitry Konstantinov:

Dear all, 

I have tried to implement more mature DB schema. The source SQL code for it can found it here:

    https://gitlab.cern.ch/PhysicsValidationDB/g4db/blob/master/schema.sql

The picture illustrating the schema can be found at the same git repo, it is also attached to this mail.

I have introduced two data types to keep data - histograms and chart plots:
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[]
);
and 
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[]   
);
but actually I prefer different naming - "scatter plot" or "graph", the corresponding class in ROOT is called "TGraphErrors" )

It seems that having this two types of containers we can seized all possible experimental and simulation data.

I have put few examples into svn, please take a look send your comments and concerns:
 
     https://gitlab.cern.ch/PhysicsValidationDB/g4db/blob/master/insert.sql

The schema is getting better but I am not sure that I am happy with referencing from DATATABLE to different "dictionary" tables based on "id".
My opinion that it should use "name" instead if "id". And maybe we even have to rid of "id"s in the following "dictionary" tables like MODEL, TARGET,
OBSERVABLE, SECONDARY, REACTION. It would nice to have "real" name in this table. It will simplify querying of the DB. 
Please comment. 

CREATE TABLE DATATABLE (
   ID       SERIAL  PRIMARY KEY,
   TESTID     INTEGER REFERENCES TEST(ID),                             -- REFERENCES THE TEST THAT TEST RESULT IS ASSOCIATED WITH
   MCTOOL     INTEGER REFERENCES MCTOOL(ID),                           -- REFERENCES TO PROGRAM USED
   MODEL      CHARACTER VARYING(50) REFERENCES MODEL(name),            -- REFERENCES TABLE WITH THE GEANT 4 MODEL OR PHYSICSLIST USED
   BEAM       INTEGER REFERENCES BEAM(ID),                             -- REFERENCES PARTICLE TABLE (PDGID,NAME, ...)
   TARGET     CHARACTER VARYING(50) REFERENCES MATERIAL(name),         -- REFERENCES TABLE defining materials
   OBSERVABLE CHARACTER VARYING(50) REFERENCES OBSERVABLE(name),       -- REFERENCES TABLE defininG OBSERVABLES (CROSS SECTION, MOMENTUM OF OUTGOING PARTICLES..)
   SECONDARY  INTEGER REFERENCES PARTICLE(PDGID),                      -- REFERENCES PARTICLE TABLE (PDGID,NAME, ...)
   REACTION   CHARACTER VARYING(50) REFERENCES REACTION(NAME),         -- REFERENCES REACTION TABLE (SCATTERING, PARTICLE PRODUCTION, CAPTURE, DECAY....)
-- DATA_TABLE INTEGER REFERENCES DATA_TABLE(ID),                       -- REFERENCES 1d HISTOGRAM
   PAPER      INTEGER REFERENCES PAPER(INSPIREID),                            -- REFERENCE to original paper with inspire, id, experiment, authors etc 
   STATUS     INTEGER REFERENCES ACCESS(ID),                           -- controls who has access to the test result
   KEYWORDS   VARCHAR(50)[]                                            -- keywords associated with the testresult e.g. angle of outgoing particle
);

Of course, we have to implement many small checks (CHECKS, NOT NULL etc) to insure proper and consistent records in the DB. 

I would propose to work on this proposal altogether using git (you all have access to it). It will allow us to conclude much faster.

Then I have a couple of question for Hans.

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.

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

Thank you! See you tomorrow.

Cheers,
   Dmitri