Organization of the Database
The database has more than 60 tables (in 2023). Here are some explanations to help you understand more quickly how it is organized
Grottocenter is a project that was developed in 2013, in 2021 we changed the organization of the database. The implementation of APIs required the use of a clearer vocabulary for users. There are therefore differences between what is in the Database and what is really used, between the names displayed by the APIs and the names of tables or fields. Fortunately a page will help you understand everything
Tables are prefixed with a t, an h or a j
The t_ tables contain the data. The h_ tables contain the history of changes on the t_ tables. The h_ tables are identical to the t_ tables because it is on these tables that the creations / modifications take place. Any modification on the h_ table is then copied to the t_ table by a trigger. A page explains how it works in detail
The j_ tables are junction tables
The name is pretty clear normally
Some fields are present in many tables
id_author corresponds to the person who created the element and date_inscription to the date of this creation id_reviewer and date_reviewed correspond to information related to the last modification. All intermediate interventions are accessible in the corresponding h_ table
In Grottocenter nothing is deleted, we pass the is_deleted field to 1 Often when an element is deleted it is because it corresponds to a duplicate. We will register the id of the object which is kept in redirect_to
Here is a diagram... different from the one we learn at school, simpler I hope
- Caves can be connected to several entrances
- Each entrance can be linked to several location, description, history, rigging, comment, which are the detailed information associated with the entrance
- Caves can currently be associated with a description but they should soon be associated with all elements except location. Indeed there can be specific information for a particular entry and general information for the whole network
- Each entry, network, organization, massif can have several names stored in the t_name table, but for now the application only manages one. Among the names there is one that is declared main
- id_language refers to a row in the t_language table that contains all languages recognized as official languages in at least one country
- id_country refers to a row in the t_country table that contains a list of countries recognized by the UN. It is not updated automatically
- The t_document table has other identifiers to explain, but document management deserves a separate explanation
This table has received data from the historical BBS and from the previous version of Grottocenter. some historical data will be phased out
- the junction tables allow to associate a document with several authors, several massifs, several regions (defined by the BBS and more up to date), several languages, several subjects.
- Currently it is only possible to associate a document with an entrance or a cellar, a publisher, a library, a license, a type
- The license corresponds to a row in the t_licence table that has been completed with the licenses defined in Karstlink
- The document type corresponds to a row of the t_type table which has been completed with the different types of documents selected for Karstlink
- -old- fields are historical data that is displayed in Grottocenter but no longer populated
- A document can be associated with several files that have a type defined in t_file_format. This table contains the formats that were retained because they seemed relevant. this list is expected to evolve according to the needs
Here are some answers before you ask the question...
Entrances are points on the map, we can locate them. It's normal that they have coordinates. The cave is the underground part, it cannot have a location, but we need to indicate them on the map when they are connected to several entrances (a network). The coordinates of the caves are therefore the average of the coordinates of the entrances associated with it. The coordinates of the cave and the entrance are identical if the cave is connected to a single entrance.
And why are you storing latitude and longitude rather than a point? It's something to do, we hope to be able to change this documentation quickly