Structuring rare-earth mineral data in an OpenOffice™ Base table

A contribution by Axel Drefahl, axeleratio.com, September 11, 2016.


My previous post “Exploring OpenOffice Base: an example involving rare-earth mineral data” demonstrated how to get started with OpenOffice™ Base (ooBase) and create a simple table. The table creation procedure is: (1) design the table in design view and (2) enter data in spreadsheet view (data view). If you are familiar with worksheets in spreadsheet applications, you know how to interact with an ooBase table. The interesting and useful development comes with table relations and task-specific table navigation to find data and synthesize information. The latter, however, depends on well-structured, resourceful data. Here, we “transfer” a table of  rare-earth mineral names associated with compositional formulas into an ooBase table we call CesbronList—named after F. P. Cesbron, who published this mineral/formula list as an appendix in a workshop contribution on rare-earth elements [1].

Designing the table CesbronList

We begin like we did with table MineralClasses by selecting Create Table in Design View… to open the Table Design window. Our first Field Name is MinName for the column of mineral names. The associated Field Type is Text [ VARCHAR ]. We stay with the default text length of 50, displayed in the Field Properties panel. The next Field Name is MinFormula, again with Field Type Text [ VARCHAR ]. In the Field Properties panel, we change the text length to 75 to make sure we have enough space for those long formulas with multiple subscripts and element-variation lists. These two columns would suffice to capture the mineral names and formulas of Cesbron's list. But we will include further columns to manage data handling based on mineral composition and mineral classification.

The third Field Name is isSFN with Field Type Yes/No [ BOOLEAN ]. This is a boolean descriptor indicating whether the given formula is compatible with the Stoichiometric Formula Notation (SFN) format or not. SFN-compatible formulas can be further processed with CurlySMILES-capable software [2].

The following 16 fields also are of Field Type Yes/No [ BOOLEAN ]. Their entries indicate whether the formula (and thus the mineral) contains a specific rare-earth element. The self-explanatory field names are: hasY, hasLa, hasCe, hasPr, hasNd, hasPm, hasSm, hasEu, hasGd, hasTb, hasDy, hasHo, hasEr, hasTm, hasYb and hasLu.

Finally, the last Field Name is MinClassID with Field Type Integer [ INTEGER ]. The MinClassID column contains secondary keys; each one matching one in the primary key column of table MineralClasses to refer to the mineral class of the respective mineral..

Unless you haven't done so by adding the fields, you now want to change Entry required from No to Yes for each boolean field to make real boolean; other wise you would have No or Yes or unspecified. Click into each Field Type cell and change to Yes in the Field Properties panel. You probably want to do the same for some other fields such as MinName, but not for those fields with a possibility to be left empty during initial data entry but to be updated at a later data-entry stage.

The table design is complete. Under the File menu, select Save, name the table CesbronList and press OK. Again, create a primary key with AutoValue Yes. Close the Table Design window.

Creating the table CesbronList

Now we need to fill in the data. The steps are:

1. Click Tables in the Database panel,
2. Open CesbronList drom the Tables panel,
3. Enter your data,
4. Select Close from File menu and press Yes if asked to save changes.   

You don't need to enter the data yourself. Instead, you can download a version containing the two tables MineralClasses and CesbronList.

Downloadable version: www.axeleratio.com/gems/db/REE/try/RareEarthMinerals_2tables.odb.

Use this version as a test base and starting point for the following steps in developing your rare-earth mineral database. 

A section of table CesbronList

Keywords: open source, ooBase, table design, design view, spreadsheet view, primary key, secondary key, field properties.

References
[1] “Mineralogy of the Rare-Earth Elements” by F. P. Cesborn in: P. Möller, P. Černý and F. Saupé. Lanthanides, Tantalum and Niobium. Springer-Verlag, Berlin Heidelberg, Germany, 1989.
[2] Axel Drefahl: CurlySMILES: a chemical language to customize and annotate encodings of molecular and nanodevice structures. Journal of Cheminformatics 2011, 3:1.
DOI: 10.1186/1758-2946-3-1.