Skip to content

OctoSAM data model conventions

This document describes the naming and design conventions used in the OctoSAM database schema. Following these conventions ensures consistency and makes it easier to understand the data model structure.

Naming conventions

All names use PascalCasing mostly according to SQL Server and .NET naming standards.

Table names

Table names use singular form (not plural).

Examples: - The table that holds machines is called Machine (not Machines) - Users are stored in the User table

Field names

Field names should be self-explanatory in most cases. Whenever possible, we use an established standard name, mostly inspired from the Microsoft Windows terms and conventions. We use abbreviations only where they are well established. Generally we do not invent abbreviations.

Examples of standard field names:

  • Name, DisplayName, Description
  • SerialNumber, AssetTag, HostName
  • Manufacturer, Model, Version

Framework tables and columns

OctoSAM uses some 3rd party components that require database tables for persistence. For such tables, the conventions of the framework are followed as closely as possible, even when OctoSAM conventions would demand a different naming.

Primary keys

All OctoSAM tables must have a primary key called ID of type bigint.

Almost all tables use automatic identity specifications maintained by the database (auto-incrementing values). Very few tables, mostly lookup tables that mirror enum values, do not have an identity spec. Their numeric ID is hardcoded by OctoSAM to maintain consistent values across installations.

Object Guids

The main objects such as User or Machine have an additional identity column named Guid. The Guid can be used to link to an object from outside of OctoSAM and remains stable even if the object is moved between databases. The Guid typically appears in the URL to an object's main web page.

Example: A machine with ID=12345 might have Guid='a1b2c3d4-e5f6-7890-abcd-ef1234567890'

Referential integrity conventions

Foreign key columns are named with the referenced table name and 'ID' suffix.

Standard examples:

  • Machine.OrganizationID references Organization.ID
  • Machine.ManufacturerID references Manufacturer.ID

Exceptions from this rule are sometimes permissible to document the specific kind of relation, or when there are multiple relations to the same foreign key table.

For example, a machine can relate to both the Most Frequent User and the Last User. The columns are named accordingly:

  • Machine.MostFrequentUserID references User.ID
  • Machine.LastUserID references User.ID

Referential integrity constraints

All referential integrity rules are actively checked by the database using foreign key constraints. Cascading deletes are implemented in the database access logic (application layer), not in the database itself. This provides better control and logging of deletion operations.

Referential integrity indexes

All foreign keys are indexed by convention.

Collations and sorting

The database uses the Latin1_CI_AS collation for all text fields. Comparison is case-insensitive but accent-sensitive. Note that the OctoSAM software may be case sensitive in some cases, but the database is not.

Special column name conventions

PrintableName

Most main entities have a column called PrintableName. This column is always a computed or derived combination of other attributes. PrintableName should be used to represent an object in queries and reports. The composition of PrintableName can be site-specific.

Examples:

  • For a Machine: "desktop-abc123 (John Doe)" combining HostName and user
  • For a User: "Doe, John (IT Department)" combining name and department

OctopusID

OctopusID is a technical identifier used by the software to reference a record internally. This allows changing the Name or DisplayName fields without having to update hard-coded references in the software.

Example: A software category might have OctopusID='STD' while its DisplayName could be "Standard".

LastScan

LastScan is a timestamp that is taken from the imported .scan data. Since it comes from other machines, it may be unreliable, for example if that machine's clock is not synchronized with the server.

Created

Created contains the date/time when the record was created in the OctoSAM database. This can differ significantly from LastScan, which represents when the source machine was last scanned.

SortOrder

Some lookup tables may have a numeric SortOrder field that indicates the standard sorting order for displaying values. Reports and queries should respect the SortOrder field when available, rather than sorting alphabetically.

Example: Status values might be ordered by workflow progression (New=1, In Progress=2, Completed=3) rather than alphabetically.