This article provides an introduction to Fuuz schema design standards.

Custom Schema

• Always start with a standard MES schema

  • Add fields or tables as needed

  • (Optionally) Consider suggesting changes to the standard package

• Search for schema before adding a new schema

• Never delete any standard MES schema

  • Someone else may need it in the future

  • Skip screen generations for anything not being using

• A copy of integrations (aka dataflows) pushing the standard data into tables will be required for our standard packages

  • Example: pushing basic customer data into standard tables

  • Export a version with any custom fields or tables removed

Example Schemas

Documentation

Documentation is auto-generated for the entire API model. 

• Some screens may rely on the documentation for field level help

• Each table must have a description, including any triggers, unique indexing constraints, and exemptions from API methods

• Each column must have a description, including any default values and exemptions from API methods

Label Field

• Every table must have a meaningful label field

• In most cases that will not be ID

Data Change Capture Retention Days

• The default is 120 days

• It is recommended that data changing on a regular basis be lowered

• Data in tables with a corresponding history table must adjust this value as well

  • Example: Current Tag Values versus Tag Value History tables

TTL

• Data retention must be taken into account for each table

• TTL indexes must be added for data that does not need to be kept for long periods of time

Mutations

• Disable mutation methods with caution; consider security policies instead

  • It may not be beneficial to prevent everyone from updating a data model

• Prepare to explain any mutation edits during an internal review

• If you prevent updates to an entire model, turn off the Data Change Capture option

Column Naming

• All DateTime fields must end with At

  • Examples: createdAt, recordedAt, updatedAt, measuredAt

• All ID fields must reference the complete object name plus ID

  • Example: strategyId should be productStrategyId

• All reference fields need to reference the complete object name

  • Example: strategy should be productStrategy

• All booleans must be positively named to avoid double negatives

  • Example: active is true instead of inactive being false)

• Exceptions may exist when multiple links exist, but they must be well named and documented

  • Examples:  primaryProductStrategyId and secondaryProductStrategyId

• Back references

  • Can be changed if they provide more meaningful context on the relationships

  • Can be removed if they do not make sense or link to too much data 

    • Example: Inventory on a status table

  • Must be renamed to remove duplicate names with parent/child relations

    • Examples: salesOrderSalesOrderLines can become salesOrderLines

Grouping And Ordering

Sort each section to provide additional context to the importance or hierarchy of the data.

• ID field

• Required fields 

• Non-required fields

• Pairs of required ID fields followed by the object

• Pairs of non-required id fields followed by the object

• All back references