Database Structure

The Linnworks database contains many tables that are used to hold together all the information required to link inventory and orders along with all the associated data. The structures involved are complex and in the below document you can see all possible tables and how they link together.

Download Linnworks Database Structure image

Text table of Linnworks Database Structure

Query and tool for Linnworks Database Schema

Commonly requested relationships for Linnworks orders

Open_Order > Open_OrderItem > StockItem

  • Retrieve inventory items from Open Orders
-- Open Orders with Item Info
SELECT
'Channel Order ID' = o.ReferenceNum,
'Channel Order Date' = o.dReceievedDate,
'Channel' = o.Source + ' - ' + o.SubSource,
'Channel SKU' = oi.ChannelSKU,
'Qty ordered' = oi.nqty,
'LW Order ID' = o.norderID,
'LW SKU' = si.ItemNumber,
'LW Title' = si.ItemTitle
FROM
[Open_Order] o
INNER JOIN
[Open_OrderItem] oi
on o.pkOrderID = oi.fkOrderID
LEFT OUTER JOIN
[StockItem] si
on si.pkstockItemId = fkStockItemID

Order > OrderItem > StockItem

  • Retrieve inventory items from Processed Orders
-- Orders processed for a specific day
SELECT 
  'Channel Order  ID' = o.ReferenceNum, 
  'Channel' = o.Source + ' - ' + o.SubSource, 
  'Channel SKU' = oi.ChannelSKU, 
  'Qty ordered' = oi.nqty, 
  'LW Order ID' = o.norderID, 
  'LW SKU' = si.ItemNumber, 
  'LW Title' = si.ItemTitle, 
  'LW Processed Date' = o.dReceievedDate 

FROM 
  [Order] o 
  INNER JOIN OrderItem oi on o.pkOrderID = oi.fkOrderID 
  LEFT OUTER JOIN StockItem si on si.pkstockItemId = oi.fkStockItemID_processed
  
WHERE
  dProcessedOn > '2020-07-12' AND dProcessedOn < '2020-07-13'

Order Status Flags

  • [Order].bProcessed
    • 0 = UnProcessed
    • 1 = Processed
  • [Order].nStatus
    • 0 = 'UNPAID'
    • 1 = 'PAID'
    • 2 = 'RETURN'
    • 3 = 'PENDING'
    • 4 = 'RESEND'
  • [Order].Marker
    • 0 = NOT USED
    • 1 = 'Tag 1'
    • 2 = 'Tag 2'
    • 3 = 'Tag 3'
    • 4 = 'Tag 4'
    • 5 = 'Tag 5'
    • 6 = 'Tag 6'
    • 7 = 'Parked'
  • [Order].HoldOrCancel
    • 1 = Hold when bProcessed = 0
    • 1 = Cancel when bProcessed = 1