The following article outlines aspects of a design for a practical and proven geological database that hopefully provides some ideas for your own business requirements. This model has been influenced by the Mineral Resources Tasmania (MRT) drilling database design, as a direct result of my involvement with Project Tiger several years ago.
At the logical centre of the model is an entity to storecollar details – Drill hole Collars shown as one of the two entities in light blue (see Figure 1) The light blue colour indicates a spatial entity, the Collars entity representing the surface position of linear features (drill holes, costeans etc). If you are dealing mostly with geological point data: soils, rock chips, stream sediments samples and the like, you will want to focus mainly on the Sites entity, which is also shown in light blue.
The Drill hole Collars entity typically contains attributes (fields or columns) to hold values such as the 3D collar position in space (Easting, Northing, RL), and the corresponding datum e.g. GDA94, AMG66 etc. The collar azimuth and the collar dip are other key attributes. There are a number of additional attributes that you may want to store depending upon the specific nature of your own drill holes or drilling program.
The important thing to ensure is that these attributes are actually drill hole attributes and not attributes of another entity, such as samples or assays. If you find that you need to store more than one similar attribute for a drill hole e.g. drill type 1 =’percussion’ (pre-collar), drill type 2 = ‘diamond’ (tail), then this is a signal that the information is better stored in a separate Drill-Type entity (table), as illustrated in Figure 1. This would be more logically consistent, and also allow for extra fields such as depth from, depth to, core diameter (NQ, HQ etc.) to be stored to better describe the hole with depth. A single “Primary Drill type” may be a better attribute for the collar table – allowing easy query retrieval of all diamond holes for example.
The Downhole Survey entity is conceptually fairly simple. It is readily modelled as a table with depth attributes, azimuth and dip readings. You may also want to include an attribute to describe the survey method e.g. Eastman, gyro etc. In some instances an x,y and z coordinate for each down-hole depth may need to be stored in order to describe the drill hole path in three dimensions for certain applications. As this information can be considered to be derived data it may be better represented in a presentation layer, rather than physically stored as such.
A separate Sample Information entity is shown. This makes it easy to store depth from, depth to and sample type attributes, e.g. Blanks, Standards and True (core, chip etc.) samples. This allows for a hole to have many samples.
A separate Analyses entity is shown in the datamodel. This allows for a sample to have more than one (many) assays. Included in the data model is both an Analysis Type and Results entities. This provides for a quite powerful way to store any number of scientific readings, including assays results from chemical species. This essentially allows for a given sample to have many analyses of different types, which could be particularly useful for example in the academic arena in areas such as micro-probing of minerals and isotope studies. For the small explorer, it also easily allows for, say, the same piece of core to have XRF and ICP (and other) analyses stored optimally in the database. You will also notice that the Results entity has both a numeric value and a description field. The description field gives the added flexibility for analytical results to be stored that have a text-based description rather than a purely numerical result. For the small explorer or resources company, the simpler Analyses entity is probably the better choice based purely on the ease of use this simple model provides. However, for a growing exploration and mining company, it is likely that the alternate Analysis Type and Results approach to modelling analyses would be the more future-proof and powerful solution.
A Lithology entity allows your drill hole to have many lithologies i.e. many intervals with a chosen lithology. Extra fields often stored here include full description of the lithology, stratigraphic unit etc.
A separate Modifiers entity is shown in Figure 1. This design allows for a lithology to have many modifiers such as alteration, mineralisation, weathering, colours and textures. This table stores the information that the geologist collects while logging and will often see the largest variation in required fields. This table represents the centre of customisation depending upon the data capture requirements of the commodity or mineral sought. It is very common to find the greatest number of data normalisation issues surrounding the geological logging storage tables. There is a good chance that your database has only a single table to store logging data, meaning that for a given drill hole the logging interval is duplicated
The Drill hole Structures entity allows for a drill hole to have many structural readings. The most common data fields incorporated here include RQD (Rock Quality Designation), and core recovery, although any structural type feature of the drill core or costean that need to be recorded with depth is suitable for inclusion in this table.
Drill hole intersections. One often overlooked area of data storage is drill hole intersections. In many respects this is derived data – derived from the sample and assay data, but enough geological interpretation is usually required that intersections can be considered as ‘raw’ data items in many cases. It is handy to be able to store and extract a piece of information such as: DDH-97 GREENFIELDS-009 from 76 – 87 m, 11m @ 2.02 % Pb, 3.01 % Zn, which could then be exported to a variety of reports. This information could be stored in a number of formats, however free-text fields are adequate for most purposes.
In the context of this database template, a Party refers to an individual person, company or other organisation. For the purposes of a geological database, common Party-types include the drilling company that drilled the hole, the geologist or geologists(s) that logged the hole, and the resources company that initiated the hole. The Party Details entity stores the name of the Party along with a host of other information such as address and contact numbers. It is worth noting that the Party Details entity could also be referenced in an expanded database system within your business to hold personal details for many other uses. In the Organisations entity, there is a field present to store the Party-type, which in turn relates back to a record in the General Codes table. The Organisations entity acts as a link table between the drill hole and the Party table, thereby providing a Many-to-Many functionality. The Company comment field in the Organisations entity allows for a comment specific to a drill hole and a related Party to be assigned.
The Drill hole Purpose entity is depicted as a One-to-Many relationship with drill holes. A drill hole can have many Purposes in this model, another name for a purpose could be a drill hole aim, in which case there would often be multiple aims for a given exploration hole. The Drill hole Purpose entity is probably optional for many small exploration companies, as this functionality could also be accomplished using the General Comments entity, which leads us to the next discussion.
To reduce the number of tables having their own individual comments fields, the template illustrates using a General Comments entity.
This entity has a Comment Table field, which is a character format field to store the name of the table the comment relates to. Along with the Record Id field, the Comment Table field uniquely identifies the specific record to which the comment belongs. One benefit of this approach to managing comments is that no new fields need to be created in your entities each time you decide to include comments in them. The Comment-type field in the General Comments entity provides the ability to assign a standard reusable Comment-type from the General Codes table. Using the previous example, one Type you could include is a Drill hole Aim. Another example would be to create a drill hole summary Comment-type to record an overall description of the drill hole, whether the aims were achieved and whether any mineralisation was intersected.
Two simple but key entities are the code tables shown below. There is no reason that you couldn’t merge the two tables into one, but I prefer to have one code table to store drilling (geology) related codes, and another more general code table to store code values that might also be applicable to other databases or models I work on. A useful attribute in the two code tables is the Parent attribute. This allows the ability to specify a code parent-child relationships and build up a hierarchy for lookups in a geological application. For code values I have got into the habit of using three-letter codes rather than numbers, with some approximate abbreviation of the parameter where possible. This gives a measure of user-friendliness to the data when viewed in queries, and can help at times when performing data validation etc. By having all the geology codes in one Drillhole Codes table, rather than a dozen or more separate lookup tables, data management is simplified. As your business requirements inevitably evolve, insertion of new code types and records in the code tables is much simpler than having to create new physical lookup tables.
That completes my summary of a geological database template for the small explorer. If you have a geological database and/or application of your own that needs some work, or you need some professional advice and assistance, please feel free to contact me to discuss by using the Contact Me tab at the top of the page.