dim_product


Description

An article that is sold physically or virtually. It is either defined with multiple attributes beforehand or just with a name being specified ad hoc when a sale is made (further referenced as dynamic product).

Columns

Column Type Size Nulls Auto Default Children Parents Comments
product_id varchar 256 null
fct_revenue.product_id Implied Constraint R
fct_revenue_cash_based.product_id Implied Constraint R

The primary key of this table. MD5 hash generated from product_variant.id for static products, or from organization_unit_id + item_number for dynamic products.
type varchar 256 √ null

What broad type this product falls into.

  • CONSUMPTION_CREDIT: An amount given to the customer to buy physical products with
  • CONTRACT_VOUCHER: A giftcard enabling the use of a specific contract for a period of time
  • MATERIAL: Something you can physically buy in a store. (eg a Bottle, a Drink, a chocolate bar)
  • UNKNOWN: The type was not specified (in case of dynamic products)
  • VOUCHER: A general giftcard to buy either contracts or physical products with
  • VIRTUAL: The fee for taking part in a course or on an event
variant_name varchar 256 √ null

A product name can have multiple further specifications (variant_names). Given by a user. (eg 1 Liter, 1x, 20min). UNKNOWN in case of a dynamic product.
item_number varchar 256 √ null

A human readable identifier for this product. Freed up (set null) on archived products to redistribute.
archived bool 1 √ null

Permanently removed but kept for historic relations. dynamic products can’t be archived.
name varchar 256 √ null

The name given by a user to the created product. (eg MyGyms ProteinShake)
class_of_goods varchar 256 √ null

The category of the product, user-defined or UNKNOWN in case of a dynamic product.
last_updated timestamptz 35 √ null

System column. UTC Timestamp at which entry was calculated (lags shortly behind source system).

Relationships

Close relationships within degrees of separation