Drillhole Database Design

This is a tutorial on how to design a basic drillhole database using simplified SQL language. A basic design is provided in generic SQL along with the implementation explained in MS Access and SQLITE. SQL code for MySQL and PostgreSQL and design diagrams are also provided. You can use this code as starting point to implement a drillhole database.

Why SQL databases?

The drillhole database is the cornerstone of any mining project or exploration campaign. It is just a file with a bunch of data but it represents the information you require to calculate resources, reserves and many other things. Sometime the drillhole database contains information which can not be recovered. By using a bad database you may put in risk your very expensive and non-reproducible information.

What is a bad database

An example of bad database is the one stored in spreadsheets files. Microsoft (MS) Excel and similar softwares like OpenOffice and LibreOffice are not designed to ensure data integrity. Data integrity is the opposite of data corruption, which is a form of data loss and excel is good for this. To illustrate an example type in MS Excel 10E-25, this is the drillhole 25 of the section East 10; MS Excel will convert this input automatically to scientific notation as shown in Figure 1.

Figure 1
Figure 1

 

The good database

The good database has physical integrity and logical integrity. It also have other properties I’m probably missing here. The physical integrity means that you are ensuring the data will not disappear with a server crash or because inexpert hands are playing with the drillhole database. The logical integrity means that each byte in your database is correct and any new input has to be correct in order to be accepted by the database sofware. Examples logical integrity are CHECK CONSTRAINTS to:

  • avoid grade values out of range
  • avoid assigning assay values to a drillhole non-existing in the collar table
  • avoid asigning lithology codes non-predefined in a list of lithologies

Other things making good the database are the database audits, data verification and backups. The geological database may requires a certain level of ownership and this is a person responsible, for example a Qualified Person (as defined by the Canadian Institute of Mining, Metallurgy and Petroleum).

The database software

To implement a database with logical integrity the common option is the traditional relational database management system (RDBMS). For simplicity let’s call this a database software. You can use small an simple database software like Microsoft Access or SQLITE but as the database grows with your mining project you may need a SQL Server database like MySQL or PostgreSQL.

You can use server databases like MySQL or PostgreSQL in your local computer. To simplify the work you will need and administration user interface like PgAdmin or MySQL Workbench. This is like a graphic interface similar to the one in MS Access or the SQLite Studio.

Another good option are dedicated database software like AcQuire GIM Suite or Datamine Fusion. These relay on SQL servers and you may need tu understand how SQL works in order to implement these systems.

SQL databases are not the only option, Mongo DB is a non-SQL open-source, document-oriented database with a language similar to JSON.

Microsoft Access a good option for small exploration projects but not an intuitive option to learn SQL databases. For this reason in this post I will use SQLite as first option and the implementation in MS Access will be explained and provided for your convenience.

SQLite is a database software that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. It is almost as powerful as a SQL server database system but also small and simple. It works very well in android devices and a good APP for playing with databases is SQLite Manager.

Learning SQL with SQLite will allow you to easily migrate to larger SQL server systems and better understand what you are doing in MS Access.

PostgreSQL and SQLite provide support for spatial object. See for example PostGIS and Spatialite in the software QGIS.

MySQL is one of the world’s most widely used relational database system and the most widely used open-source database. It is a popular choice for use in web applications, and is a central component of the widely used LAMP open source web application software stack. Almost every IT and non-IT web developer has been exposed to MySQL which make it a good option of well known, easy to use Server Database.

Three words about SQL

If you know a bit about SQL you can skip this section.

SQL is the language for databases and it has a well-defined standards that specifies what SQL is and does, making the code almost compatible with most of the database software. Large SQL systems like Microsoft SQL, MYSQL and PostgreSQL in addition include non-standard SQL, procedural language and extension which make the code non-portable. In this post you will find only standard SQL. The SQL language emulates human languages, as shown in Figure 2. This entire line is a SELECT statement and it ends with the semicolon symbol.

Figure 2
Figure 2

The SQL language can be divided into:

  • Data Definition Language (DDL). This is to defining data structures, especially database schema. Example of DDL are CREATE, DROP and ALTER statements.
  • Data Manipulation Language (DML) is for inserting, deleting and updating data. INSERT, DELETE and UPDATE are the most important statements.
  • Data Query Language(DQL) is how you query your data. SELECT is the main statements. It may be explained as part of the DML in other references.
  • Data Control Language(DCL) define user access privileges.
  • Comments are used to add notes to your code. Tow options are available: the line comment (-- bla bla bla ) and the block comment ( /* bla bla bla */)

The drillhole database

The drillhole database is a set of tables, each one with a very specific purpose and type of information. A good practice is to separate tables with different measurement type, for example the downhole survey, the log and the assay tables may be separated because their “sampling” interval may be different. A common drillhole database may look like this:

  • drillhole definition tables
    • collar
    • survey
  • interval (FROM-TO) tables
    • lithology
    • assay
  • extra (definition) tables
    • lithology codes and descriptions
    • assays provided by the lab with assay identifier code (sample ID) and lab
      certificate code (certificate ID)

The drillhole database visual design

Before you put a single value in the database you may create the database logical design. The design consist in defining the tables, table fields and the relationship between tables. I will use for this the DBDesigner Fork because it is easy to use, free and because you can convert the design to SQL code for different databases. Figure 3 shows a simple drillhole design using enhanced Entity–relationship (EER) notation.

Figure 3
Figure 3

To adjust this model to your own needs download this file and edit it with DBDesigner Fork

Figure 4 show a table editor window with the table Collar, there you can change the field names and properties (Figure 4). NN means not null allowed, for example, you will not be able to enter a drillhole without coordinates or without length to end of hole (EOH). The primary key BHID (drillhole ID) in the table Collar is to prevent users to input two drillholes with the same name.

Figure 4
Figure 4

Now, double click the table Assay to see the properties, as shown in Figure 5. In this case you have two primary key (BHID, [From]) which means the you will not be able to put two intervals with the same BHID and From values. This will avoid duplicated sample intervals but not overlapped intervals.

Figure 5
Figure 5

Notice that the fields From and To are enclosed in squared bracket [ … ], the is is because From and To are reserved words in SQL language. Actually, this is the default notation in MS Access, for example, to refer to the field Sample_ID you can write in SQL Assay.Sample_ID or [Assay].[Sample_ID] or Assay.[Sample_ID].

To edit the relationship between Assay and Collar double click on the line which connect this two tables and you will get the dialog shown in Figure 6. This is a one to one relationship which means that the drillhole may exist in table Collar in order to input or change the name of the drillhole in Assay. Note that the BHID in Assay table is a Foreign Key (Figure 7)

Figure 6
Figure 6
Figure 7
Figure 7

Also note that we are creating Reference Definition in cascade which means that if you change the name of a drillhole in Collar it will change the name in Assay. If you delete the drillhole in Collar it will delete all the records referring to that drillhole in Assay. The relationship between the tables Lab_assay and Assay are trough Sample_ID but this relation looks slightly different because Sample_ID is a foreign key in assay but not a primary key. This is to avoid duplicates, for example, in the case of two equal [BHID, From] intervals but with different Sample ID.

From the drillhole database design diagram to SQL

To implement this design in your favorite software export the visual design to an SQL script using File\Export\SQL Create script. We don’t have MS Access here but you can use SQLITE instead, as shown in Figure 8.

Figure 8
Figure 8

Save the results to a script file (*.sql) and open the file with any text editor, preferably with code highlight, for example Notepad++. The output is just a set of CREATE TABLE SQL statements (DDL). This define the tables in SQL and the relationship between tables. Each CREATE TABLE statement is known as table schema (the table definition). The schema for the Table Assay may look like this:

CREATE TABLE Assay (
  BHID VARCHAR(45)  NOT NULL,
  [From] FLOAT  NOT NULL,
  [To] FLOAT  NOT NULL,
  Samp_ID VARCHAR(50)  NOT NULL,
  Comments VARCHAR(255),
PRIMARY KEY(BHID, [From]),
  FOREIGN KEY(BHID)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(Samp_ID)
    REFERENCES Lab_Assay(Samp_ID)
      ON DELETE CASCADE
      ON UPDATE CASCADE);

Note that the order of the table schema (CREATE TABLE clauses) in the SQL script is important, you may create Collar and then the tables using Collar.BHID as foreign key, for example Assay and Litho. In the same way you may define the tables Lithologies and Lab_Assay before the tables Assay and Litho because the fields Lithologie.Litho_ID and Lab_Assay.Samp_ID will be used as foreign keys in Assay and Litho.

Refining the database design in the SQL code

This database design is already superior to any excel database in terms of integrity. Some integrity control actions are already implemented, for example:

  • BHID may exists in the collar database before insert or update records in the any table assay with BHID, for example the Assay table
  • The lithology codes are selected from a predefined list of lithologies(the Lithologies table)
  • The lithology codes will be automatically updated in all the tables if they change in predefined list of lithologies
  • Duplicated drillhole ID are not allowed in the Collar table
  • The BHID will be updated in all the tables if they change in the Collar table
  • Null coordinates, drillholes ID, FROM-TO intervals and survey values are not allowed
  • Duplicated [BHID, From] intervals are not allowed

Some extra integrity and consistency features may be implemented, for example:

  • Introduce comments to explain each table, the field meaning, system of coordinates, grade units, etc.
  • Check consistency in the intervals [From , To]
  • Check that coordinates are withing your project area
  • Check the validity of the grade values
  • Add other necessary field like laboratory source, certificates, dates of the assays, and many others
  • Add security and control change, for example user rights to read an write
  • Store USER ID and Transactions in order to rollback in case of error
  • Define and implement backup protocol.
  • Define audits and data validation.
  • Predict future changes in the database with the evolution of the mining project
  • Add user interface like web base application or MS Access forms

Most of this bullet points can be implemented in SQL but spatial validity is difficult to implement, for example the presence of overlapped intervals require non standard SQL procedural language or an external software. This issues may be identified in regular database audits.

Adding comments and CONSTRAINT Check

The output from DB designer Fork may be manually edited in order to add comments and CONSTRAINT Check. After some manual editing of the SQL code for the Assay table schema may look like this:

/*
___________________________________________________________________

 This is the table assay,
 Au grades are in Table Lab_Assay

 Designed By: Adrian Martinez
              August, 1450.
___________________________________________________________________

*/

CREATE TABLE Assay(
BHID VARCHAR(45)  NOT NULL,      -- Drill Hole ID
[From] FLOAT  NOT NULL,
Samp_ID VARCHAR(50)  NOT NULL,   -- Linked with samp_ID in Lab_Assay
                                 -- use Lab_Assay “undefined” for
                                 -- non assayed intervals
[To] FLOAT  NOT NULL,
Comments VARCHAR(255),
CONSTRAINT chk_interv
 CHECK ([To];[From]),            -- Make sure the From, To interval is valid
PRIMARY KEY(BHID,[From]),
 FOREIGN KEY(BHID)
  REFERENCES Collar(BHID)
        ON      DELETE CASCADE
        ON      UPDATE CASCADE,
FOREIGN KEY(Samp_ID)
        REFERENCES Lab_Assay(Samp_ID)
        ON      DELETE CASCADE
        ON      UPDATE CASCADE);

With the CONSTRAINT Check we make sure the FROM – TO intervals are correct. Other check may be necessary, for example, to ensure you don’t input intervals too long use the code

CONSTRAINT chk_interv
  CHECK ([To]-[From]>0 AND [To]-[From]>400)

You may also add constraint on grade

CONSTRAINT chk_grade
  CHECK (Fe>0.2 AND Fe<70 AND Ni>0.01 AND Ni<20)  -- empiric valid intervals on grade

The constraint clause is not supported by MS ACCESS SQL language but are implemented with the tool validation rules.

Adding Data

If you use MySQL, SQLITE and many other database system you can have data and table definition in a single SQL script, this is known as Dump Files and is a standard way to create backups and transfer databases. In the MS Access sql editor you can run only one SQL statement at the time, .

To add data you need the Data Manipulation Language (DML) command INSERT. For example we can populate the collar table as follow:

-- Adding three drillholes
    INSERT INTO Collar (BHID, X, Y, Z, EOH)
    VALUES ('D1', 10, 10, 20, 15),
           ('D2', 20, 20, 20, 15),
           ('D3', 10, 30, 20, 15);

In MS Access

-- Adding three drillholes
    INSERT INTO Collar (BHID, X, Y, Z, EOH) VALUES ('D1', 10, 10, 20, 15);
    INSERT INTO Collar (BHID, X, Y, Z, EOH) VALUES ('D2', 20, 20, 20, 15);
    INSERT INTO Collar (BHID, X, Y, Z, EOH) VALUES ('D3', 10, 30, 20, 15);

And the Queries?

Sorry if I disappointed you but at this point we are only interested in the data definition part of SQL. To retrieve data you may use the command SELECT, this is well documented and you will find a lot of examples and tutorials online.

All in one

If you use SQLITE, MySQL or PostgreSQL you can create single script with data definition (DDL) and data manipulation (DML). An example is implemented the Appendix All in one with SQLITE. If you are planning to use MS Access you can’t do this.

Testing the design

Finally you may test the database to make sure it works as expected. The test consist in inserting some dummy data to see if the result is as expected. In addition you may want to test the integrity by trying to input bad data. Let’s run an example steep by steep using SQLITE.

Testing the implementation in SQLITE

First download the latest version SQLiteStudio and open the main executable, installation is not required for this software, then create a new database as shown in Figure 9. Connect your database as shown in Figure 10 and open a SQL editor by pressing the keys Ctr + E.

Figure 9
Figure 9
Figure 10
Figure 10

Copy the SQL code from the Appendix all in one with SQLite, select the portion of the code you want to execute (or select ALL) and press F9 key to run the selected code (Figure 11). You will see new tables and columns created in the left panel (Figure 11).

Figure 11
Figure 11

To test the design use the code provided in the second part of the Appendix all in one with SQLite. A reduced version of this code look like this:

/*
___________________________________________________________________

    Testing Area
___________________________________________________________________

*/

-- Testing collar
    -- this may work
    INSERT INTO Collar (BHID, X, Y, Z, EOH)
    VALUES ('D1', 10, 10, 20, 15),
           ('D2', 20, 20, 20, 15),
           ('D3', 10, 30, 20, 15);

    -- this may rise an error
    INSERT INTO Collar (BHID, X, Y, Z, EOH)
    VALUES ('D1', 10, 10, 20, 15);

-- add some data in lab assay and lithologies
    -- this may work
    INSERT INTO Lab_Assay
    VALUES ('sample1', 15.5),
           ('sample2', 5.5),
           ('sample3', 0.05);

    INSERT INTO Lithologies (Lithology_ID, Description)
    VALUES ('G', 'Granite'),
           ('B', 'Basalt'),
           ('OB', 'Oberburden');

    -- this may work
    INSERT INTO Litho (BHID, [FROM], [TO], Lithology_ID)
    VALUES ('D1', 0, 10, 'OB'),
           ('D1', 10, 20, 'B'),
           ('D1', 20, 30, 'G');

    -- this may fail, YYYY not in Collar
    INSERT INTO Litho (BHID, [FROM], [TO], Lithology_ID)
    VALUES ('YYYY', 0, 10, 'OB');

    -- this may fail, lithology id anorthosite not exist in Lithologies
    INSERT INTO Litho (BHID, [FROM], [TO], Lithology_ID)
    VALUES ('D1', 50, 100, 'anorthosite');

In this example some integrity issues were identified.

Appendix: All in one with SQLITE

This is the code implemented for SQLITE V3

/*
___________________________________________________________________

 DRILLHOLE SQL DEFINITION

 Created:     Adrian Martinez Vargas, April 2015
              http://www.opengeostat.com/

 License:     GNU General Public License V3
              http://www.gnu.org/licenses/

 Description: This is a database definition file optimized for
              SQLITE
___________________________________________________________________

*/

-- Active case sensitive and foreign support in SQLITE
PRAGMA foreign_keys = ON;
PRAGMA case_sensitive_like = True;

/*
___________________________________________________________________

    Put here the parent tables
___________________________________________________________________

*/

-- Collar Table
-- DROP TABLE IF EXISTS Collar;
CREATE TABLE Collar (
  BHID VARCHAR(45)  NOT NULL,
  X FLOAT  NOT NULL,
  Y FLOAT  NOT NULL,
  Z FLOAT  NOT NULL,
  EOH FLOAT  NOT NULL,
  Comments VARCHAR(250),
PRIMARY KEY(BHID));

-- List of valid lithologies
-- DROP TABLE IF EXISTS Lithologie;
CREATE TABLE Lithologies (
  Lithology_ID VARCHAR(20)  NOT NULL,
  Description VARCHAR(50),
  Comments VARCHAR(250),
PRIMARY KEY(Lithology_ID));

--assay as provided by the lab
-- DROP TABLE IF EXISTS Lab_Assay;
CREATE TABLE Lab_Assay (
  Samp_ID VARCHAR(50)  NOT NULL,
  Au FLOAT,
PRIMARY KEY(Samp_ID));

/*
___________________________________________________________________

    Put here the child tables (tables with foreign key)
___________________________________________________________________

*/

-- Define the Survey Table
-- DROP TABLE IF EXISTS Survey;
CREATE TABLE Survey (
  BHID VARCHAR(45)  NOT NULL,
  AT FLOAT  NOT NULL,
  AZ FLOAT  NOT NULL,
  DIP FLOAT  NOT NULL,
  PRIMARY KEY(BHID, AT),          -- no duplicates (BHID, AT) allowed
  FOREIGN KEY(BHID)               -- only BHID in the collar table are allowed
    REFERENCES Collar(BHID)
      ON DELETE CASCADE           -- Remove or rename BHID if BHID is deleted
      ON UPDATE CASCADE);         -- or changed in Collar table

-- define Lithology table
-- DROP TABLE IF EXISTS Litho;
CREATE TABLE Litho (
  BHID VARCHAR(45)  NOT NULL,
  [From] FLOAT  NOT NULL,
  [To] FLOAT  NOT NULL,
  Lithology_ID VARCHAR(20)  NOT NULL,
  Log_memo VARCHAR(255),
  Comments VARCHAR(255),
 CONSTRAINT chk_interv
    CHECK ([To]>[From]),          -- ensure valid From To intervals
 CONSTRAINT chk_litho
 PRIMARY KEY(BHID, [From]),       -- no duplicates (BHID, FROM) allowed
 FOREIGN KEY(BHID)
  REFERENCES Collar(BHID)         -- Remove or rename BHID if it changes at Collar Table
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 FOREIGN KEY(Lithology_ID)         -- Remove or rename LITHOLOGYID if it changes at Lithocode Table
  REFERENCES
     Lithologies(Lithology_ID)
   ON DELETE CASCADE
   ON UPDATE CASCADE);

-- DROP TABLE IF EXISTS Assay;
CREATE TABLE Assay (
  BHID VARCHAR(45)  NOT NULL,
  [From] FLOAT  NOT NULL,
  [To] FLOAT  NOT NULL,
  Samp_ID VARCHAR(50)  NOT NULL,
  Comments VARCHAR(255),
CONSTRAINT chk_interv
  CHECK ([To]>[From]),             -- not allow zero thickness intervals or [To]<[From]
PRIMARY KEY(BHID, [From]),         -- no duplicates (BHID, FROM) allowed
  FOREIGN KEY(BHID)
    REFERENCES Collar(BHID)        -- Remove or rename BHID if it changes at Collar Table
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(Samp_ID)
    REFERENCES Lab_Assay(Samp_ID)
      ON DELETE CASCADE
      ON UPDATE CASCADE);

/*
___________________________________________________________________

    End of the script
___________________________________________________________________
*/

This is an incomplete example of database test

/*
___________________________________________________________________

    Testing Area
___________________________________________________________________

*/
-- Testing collar
    -- this may work
    INSERT INTO Collar (BHID, X, Y, Z, EOH)
    VALUES ('D1', 10, 10, 20, 15),
           ('D2', 20, 20, 20, 15),
           ('D3', 10, 30, 20, 15);

    -- this may rise an error
    INSERT INTO Collar (BHID, X, Y, Z, EOH)
    VALUES ('D1', 10, 10, 20, 15);

-- add some data in lab assay and lithologies
    -- this may work
    INSERT INTO Lab_Assay
    VALUES ('sample1', 15.5),
           ('sample2', 5.5),
           ('sample3', 0.05);

    INSERT INTO Lithologies (Lithology_ID, Description)
    VALUES ('G', 'Granite'),
           ('B', 'Basalt'),
           ('OB', 'Overburden');

    -- this may fail
    INSERT INTO Lab_Assay
    VALUES ('sample1', 15.5);

    INSERT INTO Lithologies (Lithology_ID, Description)
    VALUES ('G', 'Granite');

    -- this may work
    INSERT INTO Survey (BHID, AT, AZ, DIP)
    VALUES ('D1', 0, 10, 20),
           ('D1', 10, 10, 20),
           ('D2', 0, 20, 20),
           ('D2', 20, 20, 20),
           ('D3', 0, 30, 20),
           ('D3', 10, 30, 20);

    -- this may fails (Unique constraint)
    INSERT INTO Survey (BHID, AT, AZ, DIP)
    VALUES ('D1', 0, 10, 20);

    -- this may fail (Foreign key constraint... YYYY is not in Collar.BHID)
    INSERT INTO Survey (BHID, AT, AZ, DIP)
    VALUES ('YYYYYYY', 0, 10, 20);

    -- this may work
    INSERT INTO Litho (BHID, [FROM], [TO], Lithology_ID)
    VALUES ('D1', 0, 10, 'OB'),
           ('D1', 10, 20, 'B'),
           ('D1', 20, 30, 'G');

    -- this may fail, ('D1', 0) duplicated
    INSERT INTO Litho (BHID, [FROM], [TO], Lithology_ID)
    VALUES ('D1', 0, 10, 'OB');

    -- this may fail, YYYY not in Collar
    INSERT INTO Litho (BHID, [FROM], [TO], Lithology_ID)
    VALUES ('YYYY', 0, 10, 'OB');

    -- this may fail, lithology id anorthosite not exist in Lithologies
    INSERT INTO Litho (BHID, [FROM], [TO], Lithology_ID)
    VALUES ('D1', 50, 100, 'anorthosite');

    -- this may fail, but is not failing
    -- There is a bug, not detecting overlap...
    -- TODO implement overlap control check.
    -- interval From-To [5, 9] overlapping interval [0, 10]
    INSERT INTO Litho (BHID, [FROM], [TO], Lithology_ID)
    VALUES ('D1', 5, 9, 'B');

    -- may work
    INSERT INTO assay (BHID, [FROM], [TO], Samp_ID)
    VALUES ('D1', 0, 10, 'sample1'),
           ('D1', 10, 20, 'sample2');

    -- may fail, but is not failing
    -- There is a bug, Sample ID duplication allowed...
    -- TODO implement check or Use TRIGGERS to check
    INSERT INTO assay (BHID, [FROM], [TO], Samp_ID)
    VALUES ('D1', 20, 30, 'sample1');

/*
___________________________________________________________________

     End of the script ___________________________________________________________________

*/

Appendix all in one with MS Access

This is a partial code for MS Access. To implement this code open a SQL query view and then paste and execute each CREATE TABLE clause per separate.

/*
___________________________________________________________________

 DRILLHOLE SQL DEFINITION For Microsoft Access

 Created:     Adrian Martinez Vargas, April 2015
              http://www.opengeostat.com/

 License:     GNU General Public License V3
              http://www.gnu.org/licenses/

 Description: This is a database definition file optimized for
              Microsoft Access.

              Microsoft Access do not support multiple lines,
              you may copy and execute each statement per separate
              in a SQL view.
___________________________________________________________________
*/

-- create tables in the same order

-- Collar Table
CREATE TABLE Collar (
  BHID VARCHAR(45)  NOT NULL,
  X FLOAT  NOT NULL,
  Y FLOAT  NOT NULL,
  Z FLOAT  NOT NULL,
  EOH FLOAT  NOT NULL,
  Comments VARCHAR(250),
PRIMARY KEY(BHID));

-- List of valid lithologies
CREATE TABLE Lithologies (
  Lithology_ID VARCHAR(20)  NOT NULL,
  Description VARCHAR(50),
  Comments VARCHAR(250),
PRIMARY KEY(Lithology_ID));

--assay as provided by the lab
CREATE TABLE Lab_Assay (
  Samp_ID VARCHAR(50)  NOT NULL,
  Au FLOAT,
PRIMARY KEY(Samp_ID));

-- Define the Survey Table
CREATE TABLE Survey (
  BHID VARCHAR(45)  NOT NULL,
  AT FLOAT  NOT NULL,
  AZ FLOAT  NOT NULL,
  DIP FLOAT  NOT NULL,
  PRIMARY KEY(BHID, AT),
  FOREIGN KEY(BHID)
    REFERENCES Collar(BHID));

-- ON DELETE CASCADE and ON UPDATE CASCADE not supported
-- add this manually on Database tools/relationships

-- define Lithology table
CREATE TABLE Litho (
  BHID VARCHAR(45)  NOT NULL,
  [From] FLOAT  NOT NULL,
  [To] FLOAT  NOT NULL,
  Lithology_ID VARCHAR(20)  NOT NULL,
  Log_memo VARCHAR(255),
  Comments VARCHAR(255),
 CONSTRAINT chk_litho
 PRIMARY KEY(BHID, [From]),
 FOREIGN KEY(BHID)
  REFERENCES Collar(BHID),
 FOREIGN KEY(Lithology_ID)
  REFERENCES
     Lithologies(Lithology_ID));

-- CONSTRAINT chk_interv CHECK ([To]>[From]) not supported use validation (Property Sheet) rule to update
-- ON DELETE CASCADE and ON UPDATE CASCADE not supported
-- add this manually on Database tools/relationship

-- define Assay table
CREATE TABLE Assay (
  BHID VARCHAR(45)  NOT NULL,
  [From] FLOAT  NOT NULL,
  [To] FLOAT  NOT NULL,
  Samp_ID VARCHAR(50)  NOT NULL,
  Comments VARCHAR(255),
PRIMARY KEY(BHID, [From]),
  FOREIGN KEY(BHID)
    REFERENCES Collar(BHID),
  FOREIGN KEY(Samp_ID)
    REFERENCES Lab_Assay(Samp_ID));

/*
___________________________________________________________________

    End of the script
___________________________________________________________________

*/

After creating the tables you may manually implement ON DELETE CASCADE and ON UPDATE CASCADE in the relationship editor of MS Access. Open the relationship editor, the tables and the relationship may appear automatically but you may drag the tables in order to obtain a nice relationship diagram like this one in Figure a1.

Figure a1
Figure a1

 

If some of the tables are missing in the diagram you may load it, the relationship with other tables may appear automatically. Double click on the relationship lines and check on the option boxes ON DELETE CASCADE and ON UPDATE CASCADE, as shown in Figure a2.

Figure a2
Figure a2

 

In order to implement the CONSTRAINT Check ([To]>[From]) open the interval tables Assay and Lithology and open the table properties by clicking property sheet (Figure a3), the property sheet will appear as a panel at the right of the table editor. Type the the validation rule [To]-[From]>0 as follow in Figure 3a.

Figure a3
Figure a3

 

Do not use the validation rule located in the field property panel to implement the CONSTRAINT CHECK ([To]>[From]), this is only valid for a single field, for example, to implement the validation for the iron grade interval Fe > 0.02% and Fe < 60%. This panel is located below the table design view.

Download

File Description
Drillhole_DB_Design.xml A simplified drillhole database design in BD Designer Fork XML format. This file was used to generate the SQL draft code of the drillhole database.
dhole_model_SQLITE.sql A simplified drillhole database schema and basic test optimized for SQLite. Note: This is for demonstration purpose, more testing and editing is recommended.
dhole_model_MSACCESS.sql A simplified drillhole database SQL schema and basic test optimized for MS Access. Note: This is for demonstration purpose, more testing and editing is recommended. More manual editing is required.
dhole_model_MSACCESS.accdb A simplified drillhole database in MS Access. Note: This is for demonstration purpose, more testing and editing is recommended. More manual editing is required.
dhole_model_MySQL.sql A simplified drillhole database schema for MySQL.
Drillhole_DB_Design_MySQL_WB.mwb A simplified drillhole database design created with reversed engineering of the script dhole_model_MySQL.sql, using MySQL Workbench.
dhole_model_PostgreSQL.sql A simplified drillhole database schema for PostgreSQL.

The last version of this files can be downloaded from the git repository

References

Print Friendly