Schema

Tables and Database Schema

The principle kind of data held in a XAFS Data Library is XAFS Spectra. Additionally, several other kinds of data can be useful to include, such as for sample preparation, measurement conditions, and so on. Of course, a key feature of a multi-spectral database is to be able to combine several spectra into a Suite of Spectra, and also to identify the people adding data to a library. Thus the XAFS Data Library contains the following main tables:

Table Name	Description
spectra	main XAS spectra, pointers to other tables
sample	Samples
crystal_structure	Crystal structures
person	People
citation	Literature or Other Citations
format	Data Formats
suite	Spectra Suites
facility	Facilities
beamline	Beamlines
monochromator	Monochromators
mode	Modes of Data Collection
ligand	Ligands
element	names of Elements
edge	names of x-ray Edges
energy_units	units for energies stored for a spectra
info	General Information, version etc

While some of these tables (spectra, sample) are fairly complex, many of the tables are really quite simple, holding a few pieces of information.

In addition there are a few Join Tables to tie together information and allow Many-to-One and Many-to-Many' relations. These tables include

Table Name	Description
spectra_mode	mode(s) used for a particular spectra
spectra_ligand	ligand(s) present in a particular spectra
spectra_suite	spectra contained in a suite
spectra_rating	People's comments and scores for Spectra
suite_rating	People's comments and scores for Suites

A key feature of this layout is that a Suite is very light-weight, simply comprising lists of spectra. Multiple suites can contain an individual spectra, and a particular spectra can be contained in multiple suites without repeated data.

The tables are described in more detail below. While many are straightforward, a few tables may need further explanation.

Spectra Table

This is the principle table for the entire database, and needs extensive discussion. Several of the thorniest issues have to be dealt with in this table, making this likely to be the place where most attention and discussion should probably be focused.

--
create table spectra (
	id integer primary key
	name text not null, 
	notes text, 
	attributes text, 
	file_link text, 
	data_energy text, 
	data_i0 text default '[1.0]', 
	data_itrans text default '[1.0]', 
	data_iemit text default '[1.0]', 
	data_irefer text default '[1.0]', 
	data_dtime_corr text default '[1.0]', 
	calc_mu_trans text default '-log(itrans/i0)', 
	calc_mu_emit text default '(iemit*dtime_corr/i0)', 
	calc_mu_refer text default '-log(irefer/itrans)', 
	notes_i0 text, 
	notes_itrans text, 
	notes_iemit text, 
	notes_irefer text, 
	temperature text, 
	submission_date datetime, 
	collection_date datetime, 
	reference_used integer, 
	energy_units_id   -- > energy_units table
	monochromator_id  -- > monochromator table
	person_id         -- > person table
	edge_id           -- > edge table
	element_z         -- > element table
	sample_id         -- > sample table
	beamline_id       -- > beamline table
	format_id         -- > format table
	citation_id       -- > citatione table
	reference_id      -- > sample table (for sample used as reference
);

We'll discuss the table entries more by grouping several of them together. First, Each entry in the spectra table contains links to many other tables.

spectra Column Name	Description
energy_units_id	index of energy_units table
person_id	index of person table for person donating spectra
edge_id	index of edge table for X-ray Edge
element_z	index of element table for absorbing element
sample_id	index of sample table, describing the sample
reference_id	index of sample table, describing the reference sample
beamline_id	index of the beamline where data was collected
monochromator_id	index of the monochromator table for mono used
format_id	index of the format table for data format used
citation_id	index of the citation table for literature citation

Next, the table contains ancillary information (you may ask why some of these are explicit while others are allowed to be put in the attributes field).

spectra Column Name	Description
notes	any notes on data
attributes	JSON-encoded hash table of extra attributes
temperature	Sample temperature during measurement
submission_date	date of submission
reference_used	Boolean (0=False, 1=True) of whether a Reference was used
file_link	link to external file

Here, reference_used* means whether data was also measured in the reference channel for additional energy calibration . If 1 (True), the reference sample must be given. The *file_link entry would be the file and path name for an external file. This must be relative to the directory containing database file itself, and cannot be an absolute path. It may be possible to include URLs, ....

Finally, we have the information for internally stored data arrays themselves

spectra Column Name	Description	Default
data_energy	JSON data for energy	--
data_i0	JSON data for I_0 (Monitor)	1.0
data_itrans	JSON data for I_transmission (I_1)	1.0
data_iemit	JSON data for I_emisssion (fluorescence, electron yield)	1.0
data_irefer	JSON data for I_trans for reference channel	1.0
data_dtime_corr	JSON data for Multiplicative Deadtime Correction for I_emit	1.0
calc_mu_trans	calculation for mu_transmission	-log(dat_itrans/dat_i0)
calc_mu_emit	calculation for mu_emission	dat_iemit * dat_dtime_corr / dat_i0
calc_mu_refer	calculation for mu_reference	-log(dat_irefer/dat_itrans)
calc_energy_ev	calculation to convert energy to eV	None
notes_energy	notes on energy
notes_i0	notes on dat_i0
notes_itrans	notes on dat_itrans
notes_iemit	notes on dat_iemit
notes_irefer	notes on dat_irefer

The '''data_*****''' entries will be JSON encoded strings of the array data. The calculations will be covered in more detail below. Note that the '''''spectra_mode''''' table below will be used to determine in which modes the data is recorded.

Data Storage

As alluded to above, the data*****_ will be stored as JSON-encoded strings.

Encoding Calculations

The calculations of mu in the various modes (transmission, fluorescence) are generally well defined, but it is possible to override them by explicitly documenting the expression used to calculate mu. Note that this expression should not be expected to be fully and correctly evaluated by the database -- it is meant for human reading.