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

Commonly requested relationships for Linnworks orders

Open_Order > Open_OrderItem > StockItem

  • Retrieve inventory items from Open Orders
-- Current Open Orders received today   
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 
Where 
   CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, o.dReceievedDate))) = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GetDate()))) 
ORDER BY 
   o.dReceievedDate ASC, 
   o.norderID ASC

Order > OrderItem > StockItem

  • Retrieve inventory items from Processed Orders
-- Orders processed Today 
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 
  CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, o.dProcessedOn))) = CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))) 

ORDER BY 
  o.dProcessedOn ASC, o.norderID ASC

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