DUNE Components DB¶
This is a brief overview of how the database schema fills DUNE's hardware database requirements. It is not intended to specify any requirements, for the details refer to the Hardware Database - revised requirements The entity relationship design for this DB was built with DbSchema. Understanding an ERD will be needed. DbSchema provides some simple diagrams defining what each symbol means - see links below. This wiki briefly describes each group of tables. The goal is to provide enough data for those with a database background to determine details. It is not an attempt to explain how everything works.
General Entity Relationship Design (ERD) descriptions.
The hardware database is composed of four sections with each section color coded as shown below.
| Group | Color |
|---|---|
| Validation | Blue |
| Security | Red |
| Components | Green |
| Purchasing | Yellow |
| Miscellaneous | Tan |
There are two views of the scheama with the difference being the removal of the security tables, making it much easier to read.
Validation Tables¶
The validation tables are dull and boring. They simply ensure consistency in data entry and allow the experiment to define values against which other table fields are checked.
Security Tables¶
The security tables provide a user access to the database and define what components a user can modify. They require each user to have an FNAL Services account. The tables map users to the components which each user can edit. Once mapped the user can do all operations on that component. Each component is mapped to a role or roles. That role is assigned to one or more individuals. Also, each individual can have one or more roles. Only those marked in the users table as an administrator can add/remove users or create and assign roles.
Component Tables¶
The group of component tables, shown in green, are the heart of the database. Component data is NOT editable. If something changes you will need to enter a new timestamped record into that table. This allows us to maintain the current picture and a complete history of each component.
Tracking data for all components starts with the component_type table. This table contains a template for each specific type of component. It identifies meta-data about the component; it does not identify the individual component. For example let's assume we are adding racks and each rack supports six crates and each crate supports four modules. There would be three records in the component_types table. Something on this order where component_id is internally generated. (Not all columns are shown in the examples.)
| Component_Type_Id | Name | Properties |
|---|---|---|
| 1 | Rack Model 12a | voltage, slots: 6 |
| 2 | Crate 25C | slots: 4, current, voltage |
| 3 | Module 7AcA | some data.... |
Again it does not identify a specific component rather it identifies data about that type of component. The properties is a JSON field. Here it just shows what we can do, it does not truly show how the data will be stored.
Now that the types are defined, we can add the physical item to the database. This is done by adding the record to the components table. Here the component_id is internally generate. The component_type_id must match its type in the component_types table. The external_id is the experiment wide unique id of a physical tag which is on the property. The table below shows a few of the records added.
| Component_id | Component_type_Id | External_Id |
|---|---|---|
| 100 | 1 | 1000 |
| 200 | 2 | 1250 |
| 201 | 2 | 1300 |
| 300 | 3 | 0584 |
| 301 | 4 | 0943 |
Notice this just identifies the physical item but does not detail what its properties are. The property details are stored in the associated table called attributes. Here there would be at least one record for each physical item in the components table detailing what the values of each property is. Remember the properties detailed here were identified in the component_types table.
To this we can also add images and documents for each component. This is handled in the images table. There is no limit to the number of images and documents which can be stored for each component. Together these tables define all components.
The remaining green table is the structures table. In the real world components are joined together. For example SiPMs are attached to an FEB. The structures table allows us to track this. It tracks both the current state and past history. Using the rack data from above we can add crates to the rack and modules to crates with the records shown.
| Component_Id | Container_Id | Functional_Position |
|---|---|---|
| 200 | 100 | 1 |
| 201 | 100 | 2 |
| 300 | 200 | 1 |
| 301 | 201 | 3 |
Purchasing Tables¶
The hardware database provides optional tracking of purchase orders. The tracking includes when the order was placed, quantities requested with data for when, where and the quantity received. It does not track a history of changes to the PO, just the current state of the PO. When items on a PO are received the receipt is registered in the batches table and the items are entered into the components table. This assumes you have an external_id for the items being received.
Miscellaneous Tables¶
There are two tables which deal with testing. The component_test_types and the component_tests. These tables allow you to define a type of test and collect the results for the test on a specific component. There is no limit to the number of times a test can be run for an individual component.
The cables table is provided to identify what component each end of a cable is attached to. Cables are themselves components and require records in the component_types and components tables. Cables not editable but can be changed by adding a new record.
Feb 20, 2020