Proposed Database Schema
This database would store the asymmetry, yield, etc. for each
detector on a run-by-run basis. Therefore, in the table
labelled "run" there would be one record (or row) for each
analyzed run, assuming that each run was analyzed only once. If
a run was analyzed more than once because the cuts used to
perform the analysis were changed, then there would be multiple
records in the "run" table with the same run number but with
different cut numbers. The cut number links to a "cuts" table
which stores the value of the cuts used to analyze a given set
of runs. In order for data on a run to be stored in the
database it would have to be analyzed with a set of cuts already
stored in the cuts table. Operationally this would mean that
there would be a program used to select cuts and store them in
the database before a run was analyzed with g0analysis (perhaps
a nice GUI program using Perl and Tk). There are two different
version of the "cuts" table presented here: a simple one
consisting of the quantity to be cut on with a minimum and
maximum value and a more complex one that could perhaps be used
to construct more intricate nested and boolean cuts. The other
members of the "run" and "cuts" tables are pretty much
self-explanatory: helicity refers to the setting of the slow
helicity reversal plate, start(stop)_time refers to the time the
run began(ended), and replay_time refers to the time this
particular run was analyzed.
For each record in the "run" table there can be many entries in the "measurement" and "beam" tables. The "measurement" table stores the analyzed data for each detector, while the "beam" table stores information on beam parameters such as charge, position at target, etc. The entry detector(monitor)_id in "measurement"("beam") indicates what detector(beam quantity) the record contains data on. The ID number is an arbitrary positive integer that constitutes an index on the "detector" or "monitor" table. These tables identify the actual detector or beam quantity, store the units the data is recorded in (e.g. ppm, nanoCoulombs, etc.), and a description (the title field) to be used in output (e.g. "Octant 1 Detector 1 Asymmetry", "Beam Charge", etc.). The "type" field in theses tables refers to the type of measurement recorded, e.g. "a" for asymmetry data, "y" for yield data, "d" for difference data, "p" for pedestal data, "r" for raw data, and so on as needed. The "measurement" and "beam" tables are further indexed by timebin and stores the value and error for the quantity in the record. The combination of ID number and type forms a unique key for the "detector" and "monitor" tables while the combination of run_id, detector or monitor ID, type, and timebin does the same for the "measurement" and "beam" tables.
This Perl script creates the proposed table structure on a MySQL database server for those who would like to play around with it some. To use it you must put the location of your Perl executable on the first line and fill in the appropriate information in the section labelled "Open Connection". This script automatically fills the "detector" and "monitor" tables with appropriate records, but leaves the "run", "cuts", "measurement", and "beam" tables empty.