K3 Data Models¶

The legacy application maintains local MySQL mirrors of Navision data. These tables are written by the sync process and read by the website. All models extend MY_ActiveRecordModel and live in application/models/K3_*.php.

Legacy reference

These models belong to Zwerfkei Old. They are documented here as reference during the migration to the new platform.

K3_article — Product catalogue¶

Table: k3_articles · Synced from NAV Item table (table 27)

Field	Description
`ItemNo`	NAV item number — primary key, foreign key to website article
`Name`	Product name
`ShortDescription`	Short description
`Price`	Base price excl. VAT
`PriceIncludingVAT`	Base price incl. VAT
`Cost`	Cost price
`StockQuantity`	Total stock across all locations
`VATProdPostingGroup`	VAT group code
`Blocked`	Whether item is blocked in NAV
`ItemCategoryCode`	NAV category code
`ProductGroupCode`	NAV product group
`Brand`	Brand name as stored in NAV
`ItemBarcode`	Primary barcode / GTIN
`VendorItemNo`	Vendor's own article number
`VendorNo`	Vendor/supplier number
`SeasonCode`	Season code
`VariantFrameworkCode`	Which variant framework applies (sizes, colours)

Relations: k3_article_variants, k3_article_barcodes, k3_article_availability, k3_article_salesprices, k3_article_item_status_links, k3_article_offers

K3_article_variant — Product variants¶

Table: k3_article_variants

One row per colour + size + item combination. The Code field (variant code) is the key identifier used on order lines.

Field	Description
`ItemNo`	Parent item — FK to `K3_article`
`Code`	Variant code — used on order lines
`Description`	Variant description
`StockQuantity2`	Stock for this specific variant
`Color`	Colour dimension (dimension 1)
`Size`	Size dimension (dimension 2); third dimension (e.g. width "2E") appended on save
`ItemBarcode`	Variant barcode / GTIN
`VendorColourDescr`	Vendor's colour description
`LSItemLastSalesDate`	Date of last sale
`LSItemLastPurchaseDate`	Date of last purchase
`LogicalOrder`	Display sort order

K3_article_barcode — Barcodes / GTINs¶

Table: k3_article_barcodes

All barcodes for an item including unit-of-measure variants.

Field	Description
`ItemNo`	Parent item
`ItemBarcode`	Barcode / GTIN
`UnitOfMeasure`	Unit of measure code
`Quantity`	Quantity per UOM

K3_article_availability — Stock levels¶

Table: k3_article_availability

Stock per item, variant, and warehouse location.

Field	Description
`ItemNo`	Item number
`VariantCode`	Variant code
`LocationCode`	Warehouse or store location
`Quantity`	Available quantity
`Date`	Availability date
`Lastupdate`	Last updated timestamp

K3_article_salesprice — Price table¶

Table: k3_article_salesprices · NAV table 7002

Field	Description
`ItemNo`	Item number
`SalesCode`	Sales price group or customer code
`CurrencyCode`	Currency (blank = default)
`StartingDate`	Price valid from
`EndingDate`	Price valid until (null = open-ended)
`UnitPrice`	Price excl. VAT
`UnitPriceIncludingVAT`	Price incl. VAT
`MinimumQuantity`	Minimum quantity for this price to apply
`VariantCode`	Variant-specific price (null = all variants)

K3_article_item_status_link — Item status¶

Table: k3_article_item_status_links · NAV table 11176114

Field	Description
`ItemNo`	Item number
`VariantCode`	Variant code
`StatusCode`	NAV status code

K3_article_line_discount — Line discounts¶

Table: k3_article_line_discounts

Field	Description
`ItemNo`	Item number
`SalesCode`	Sales group or customer code
`VariantCode`	Variant-specific (null = all)
`DiscPercFromStdPrice`	Discount percentage off standard price
`MinimumQuantity`	Minimum quantity to qualify

K3_article_offer — Promotional offers¶

Table: k3_article_offers

Field	Description
`No`	Offer number (PK)
`Description`	Offer description
`Status`	Active / inactive
`OfferType`	Type of offer
`PriceGroup`	Target price group
`DealPrice`	Deal price
`StartingDate`	Valid from
`EndingDate`	Valid until

K3_periodicDiscount — Periodic promotions¶

Table: k3_periodicDiscounts · NAV table 99001453

Bundles

Website bundles are built from K3 periodic discounts of type MixMatch.

Field	Description
`No`	Discount number (PK)
`Description`	Promotion description
`Status`	Active / inactive
`Type`	`Multibuy` / `MixMatch` / `DiscOffer` / `TotalDiscount` / `TenderType` / `ItemPoint` / `LineDiscount`
`PriceGroup`	Target customer price group
`Priority`	Processing priority
`StartingDate`	Valid from
`EndingDate`	Valid until
`DealPriceValue`	Fixed deal price
`DiscountAmountValue`	Fixed discount amount
`DiscountPercValue`	Percentage discount

K3_periodicDiscountLine — Discount line items¶

Table: k3_periodicDiscountLines

Field	Description
`LineNo`	Line number
`No`	Parent discount — FK to `K3_periodicDiscount`
`ItemNo`	Item number
`VariantCode`	Variant code (null = all)
`QuantityNeeded`	Quantity required to trigger the deal
`OfferAmount`	Offer amount
`OfferPrice`	Offer price
`DealType`	How the deal applies

K3_colour_theme — Colour mappings¶

Table: k3_colour_themes

Maps vendor/brand-specific colour codes to display colours.

Field	Description
`Code`	Colour code (PK)
`Description`	Display colour name
`VendorCode`	Vendor's colour code
`Brand`	Brand this mapping applies to
`ColorNo`	Colour number
`Status`	Active / inactive

K3_synchronization — Outbound queue¶

Table: k3_synchronization

Every record waiting to be sent to Navision, and its current status.

Field	Description
`id`	PK
`reference`	Local record PK (order ID, customer ID, etc.)
`nav_reference`	NAV document or member number
`serial_number`	Sequence number for ordering
`no_to_nav`	Document number sent to NAV
`nav_id`	ID assigned by NAV on success
`model`	PHP model name: `Order` / `Customer` / `K3_periodicDiscount`
`action`	`1` = positive · `2` = negative · `3` = default
`status`	`pending` / `ok` / `error`
`date_created`	When queued
`date_send`	First attempt timestamp
`date_ok`	NAV confirmed success timestamp

Relationships to website models¶

erDiagram
    K3_article ||--o{ K3_article_variant : "ItemNo"
    K3_article ||--o{ K3_article_barcode : "ItemNo"
    K3_article ||--o{ K3_article_availability : "ItemNo"
    K3_article ||--o{ K3_article_salesprice : "ItemNo"
    K3_article_variant ||--o{ K3_article_salesprice : "VariantCode"
    K3_periodicDiscount ||--o{ K3_periodicDiscountLine : "No"

    Article ||--o{ Article_connection : "id"
    Article_connection }o--|| K3_article : "ItemNo"

    K3_synchronization }o--|| Order : "reference (ordernr)"
    K3_synchronization }o--|| Customer : "reference (id)"

Legacy Integration overview Sync Triggers & Cron Methods