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