Overall Structure

The overall structure of the Odyssey database is shown below:

Fields

Values & Units Tables

The values and units tables are one of the big strengths of the Odyssey framework, as they allow efficient and flexible storage of physical measurements in numerical, string or array forms. The units tables categorise units into different types, and conversions between units are accessible through the linear parameters defined with each unit.

While a base unit system is provided, users can add more units that are pertinent to their application if they need to.

Unit Type

This table defines the different types of units that are stored in the Unit table. Each UnitType entry contains the following data:

Column Name	Type	Description
name	Text	The name of the unit type

Unit

As its name implies, the Unit table stores all of the units along with their conversion factors. To simplify unit conversion, we define a base unit for each unit type. For each unit, we then define the factors to convert from that unit back to the base unit. We use the following formula to convert a value in an arbitrary unit \(x\) to a value \(y\) in the base unit:

\[ y = \frac{\left(x + x_{offset} \right) \cdot multiplicand}{denominator} + y_{offset} \]

Each Unit entry stores the following data:

Column Name	Type	Description
name	Text	The name of the unit
plural_name	Text	The plural name of the unit
space_after_value	Bool	Specifies if a space should be added between the value and the unit when printing the quantity (value and unit)
base_unit	Unit	Link to the base unit of the unit type (will be null if the entry is the base unit)
abbreviation	Text	Unit abbreviation
create_ts	Date & Time	When this entry was created
x_offset	Decimal	Value of \(x_{offset}\) compared to base unit
y_offset	Decimal	Value of \(y_{offset}\) compared to base unit
multiplicand	Decimal	Value of \(multiplicand\) compared to base unit
denominator	Decimal	Value of \(denominator\) compared to base unit

Value

As multiple different types of values can be stored (strings, integers, decimal numbers and array), the Value table offers a single point where users can access this data. We have created this table so that developers that interact with Odyssey and its APIs have a high-performance (database-level) access point to values of any type (hence removing any logic that would required to check the desired type before querying the database). This can become very useful when searching for values, and is already used within the Odyssey database when another table links to a value.

Each Value entry is automatically created in the database when a new String, Integer, Decimal or Array is created and each Value entry contains the following data:

Column Name	Type	Description
content_type	-	The type of content that the entry is linking to (needed for database logic)
object_id	Integer	The id of the object we are linking to
content_object	-	This is the content of the object (this is what developers would need to access through this table)
create_ts	Date & Time	When this entry was created

String

The String table contains all of the stored strings that are to be used as values. While using strings for containing numerical values is discouraged (there are specific tables for this). The odd case can still arise where a string is the only possible usable type.

Each String entry stores the following data:

Column Name	Type	Description
string	Text	The text of the string
create_ts	Date & Time	When this entry was created
value	Value	The value that the entry is linked to

Integer

The Integer table stores integer values that can be linked to a unit (although this is optional).

Each Integer entry stores the following data:

Column Name	Type	Description
integer	Integer	Integer value
unit	Unit	A linked unit (can be left blank)
create_ts	Date & Time	When this entry was created
value	Value	The value that the entry is linked to

Decimal

The Decimal table stores any float number that can be linked to a unit (although this is optional).

Each Decimal entry stores the following data:

Column Name	Type	Description
decimal	Float	The decimal number's value
unit	Unit	A linked unit (can be left blank)
create_ts	Date & Time	When this entry was created
value	Value	The value that the entry is linked to

Array

The Array table stores any array of numbers that can be linked to a unit (although this is optional).

Each Array entry stores the following data:

Column Name	Type	Description
array	Array[Float]	The array value
unit	Unit	A linked unit (can be left blank)
create_ts	Date & Time	When this entry was created
value	Value	The value that the entry is linked to

Version

As configurations, software and hardware typically have versions assigned to them, we create a Version table to store all of these versions. We enforce the use of semantic versioning numbers with major, minor and patch number values.

Each Version entry stores the following data:

Column Name	Type	Description
name	Text	An associated name of the version
major	Integer	The major version number
minor	Integer	The minor version number
patch	Integer	The patch version number
create_ts	Date & Time	When this entry was created
description	Text	An optional description of the version

Range

Typically when defining specifications, different ranges are defined that give a minimum and maximum bounds to the spec. The Range table is meant to hold such data.

Each Range entry stores the following data:

Column Name	Type	Description
name	Text	Name of the range
lower	Value	Lower value
upper	Value	Upper value
create_ts	Date & Time	When this entry was created

Hardware Tables

Hardware Model

The HardwareModel table is meant to store the model of the assembled hardware. It is meant to store the assembly logic (which part goes where, and where different sub-assemblies are).

Each HardwareModel entry stores the following data:

Column Name	Type	Description
name	Text	Name of the hardware
position	Text	The position of the hardware in its assembly
parent	HardwareModel	The parent assembly (this will be empty if this is the top assembly)
version	Version	The version of this entry
create_ts	Date & Time	When this entry was created

Hardware

The Hardware table is meant to store all of the instances of physical hardware that were built. Each Hardware element is meant to reference a HardwareModel entry.

Each Hardware entry stores the following data:

Column Name	Type	Description
serial_number	Text	The serial number (or part number) of the hardware
model	HardwareModel	The hardware model the this entry references
set	Integer	The set in which this entry belongs (can be left blank)
create_ts	Date & Time	When this entry was created

Order

As hardware can be treated in different ways in production, (initial build-up, repair, service, etc.), we create order numbers to keep track of the different types of treatment a piece of hardware with the same serial number can go through. We call these Order numbers.

Each Order entry stores the following data:

Column Name	Type	Description
number	Integer	Order number
hardware	Hardware	Link to the hardware in question
order_type	Text	The type of order we are fulfilling
create_ts	Date & Time	When this entry was created

Equipment

Throughout production, different equipment is used. Typically in a production environment we wish to keep track of the equipment used. This is what the Equipment table is for.

Each Equipment entry stores the following data:

Column Name	Type	Description
name	Text	Name of the equipment
number	Integer	Equipment number (in case there are more than one of the same equipment)
calibration_ts	Date & Time	Date and time of when the equipment was last calibrated
parent	Equipment	The parent equipment (this will be empty if this is the top assembly)
status	Text	This field is meant to show if the equipment is being used, in calibration, or is free to use
create_ts	Date & Time	When this entry was created

Production Tables

Production Step Model

Assembly and testing of hardware does not happen in a single step! The ProductionStepModel table is meant to hold all of the steps required for any assembly, repair, calibration or service procedure. Similarly to HardwareModel, it contains all of the entries that the production steps will refer to.

Each ProductionStepModel entry stores the following data:

Column Name	Type	Description
name	Text	The name of the production step model
parent	ProductionStepModel	The parent step model (so sub-steps can be added if necessary)
version	Version	The version of this step model entry
step_number	Integer	The step number (so that the order of each entry can be saved)
optional	Bool	Marker if the step is optional or not
create_ts	Date & Time	When this entry was created

Production Step

The ProductionStep table is meant to store all of the production steps that actually happened. This allows the production team to keep track of where each piece of hardware that is being built is, and what progress is being made.

Each ProductionStep entry stores the following data:

Column Name	Type	Description
order	Order	The order of the hardware in question
production_step_model	ProductionStepModel	The production step model that this production step refers to
status	Text	The current status of this production step
operator	User	The operator executing this step
start_ts	Date & Time	The date and time of when this production step was started
end_ts	Date & Time	The date and time of when this production step was completed

Configuration

As with any automated handling of hardware, the configuration of an assembly tool, testing jig, or other material can have different configurations set. This is what the Configuration table is meant to store.

Each Configuration entry stores the following data:

Column Name	Type	Description
name	Text	The name of the configuration
parent	Configuration	The parent configuration
value	Value	The value of the configuration
hardware_model	HardwareModel	The hardware model that this configuration applies to
production_step_model	ProductionStepModel	The production step model that this configuration applies to
version	Version	The version of the configuration
description	Text	Description of the configuration
create_ts	Date & Time	When this entry was created

Testing Tables

Throughout production, various tests are run on the hardware in question to ensure a certain level of quality specified by the intended customer. The tables below are meant to store this data as well as any non-compliances that can come up.

Measurement

The purpose of this table is self-explanatory (it stores the measurements taken on a specific piece of hardware).

Each Measurement entry stores the following data:

Column Name	Type	Description
parent	Measurement	The parent measurement
name	Text	The name of the measurement
value	Value	The value of the measurement
create_ts	Date & Time	When this entry was created
production_step	ProductionStep	The production step in which this measurement was taken

Specification

Each measurement that is taken is usually compared with a pre-defined specification, which is what the Specification table stores.

Each Specification entry stores the following data:

Column Name	Type	Description
name	Text	The name of the specification
valid_range	Range	The range in which we consider a measurement to be valid
applicable_scope	Range	The range in which we would want to check if a measurement is within the valid range
hardware_model	HardwareModel	The hardware to which the specification applies to
production_step_model	ProductionStepModel	The production step model that the specification applies to
group	SpecificationGroup	The specification group that this specification belongs to
description	Text	Description of this specification
severity	Text	How important this specification is
version	Version	The version of this specification
create_ts	Date & Time	When this entry was created

Specification Group

As many specifications can be defined and are typically linked together, the SpecificationGroup table allows the data owner to create different groups to link each specification together.

Each SpecificationGroup entry stores the following data:

Column Name	Type	Description
name	Text	The name of the group
commencement_date	Date & Time	The date and time that this group's specifications come into effect
expiration_date	Date & Time	The date and time that this group's specifications expire
create_ts	Date & Time	When this entry was created

Result

Typically, each raw measurement (or a group of measurements) is processed into a resulting value, which is then compared with a list of specifications. The Result table is meant to hold onto this processed data.

Each Result entry stores the following data:

Column Name	Type	Description
parent	Result	The parent result
name	Text	The name of the result
create_ts	Date & Time	When this entry was created
measurements	Measurement (Many-to-Many)	List of measurements this result was computed from
specification	Specification	The specification that this result should satisfy
value	Value	The value of this result
processor	Processor	The processor (that took the input measurements and computed this result)

Non-Compliance

A non-compliance (or NC for short) is created when a result does not meet a specification. The NonCompliance table holds all of these entries.

Each NonCompliance entry stores the following data:

Column Name	Type	Description
result	Result	The associated result
status	Text	Indicator to mark if the non-compliance has been resolved, is under review, being resolved, etc.
decision	Text	Text describing the decision made (preferably including the reason why) (mostly for documentation purposes)
reporter	User	The user that reported the non-compliance
signer	User	The user that signed to make the final decision on the non-compliance
create_ts	Date & Time	When this entry was created
close_ts	Date & Time	When the non-compliance was closed

Non-Compliance Comment

Every non-compliance would usualy spark a new discussion. This table is meant to hold the comments made on an NC.

Each NonComplianceComment entry stores the following data:

Column Name	Type	Description
parent	NonComplianceComment	The parent comment (if it exists)
non_compliance	NonCompliance	The non-compliance entry that this comment refers to
author	User	The author of the comment
content	Text	The content of the comment
create_ts	Date & Time	When this entry was created

Processor

As each measurement (or multiple measurements) might need to be post-processed into a result, the Processor table keeps track of the different post-processing scripts and actions that are taken for each measurement.

Each Processor entry contains the following data:

Column Name	Type	Description
name	Text	The name of the processor
create_ts	Date & Time	When this entry was created
version	Version	The version of the processor
production_step_model	ProductionStepModel	The production step model that this processor refers to
file_path	File path	The file path of the post-processing script