Linnworks Database Structure - Basic : Support Center

For the query and tool for viewing the full Linnworks Database Schema, please see here.

For an image display of the basic Database Structure and some commonly requested relationships, please see here.

TABLE OF CONTENTS

Order data tables
- Order
- OpenOrder
Additional order data tables
Order packaging and shipping tables
Order items tables
Stock item data tables

Order data tables

Order
pkOrderID	uniqueidentifier
cFullName	nvarchar(127)
cEmailAddress	nvarchar(255)
cShippingAddress	nvarchar(-1)
cPostCode	nvarchar(127)
dReceievedDate	datetime
dDispatchBy	datetime
dProcessedOn	datetime
fPostageCost	float
fTotalCharge	float
cCurrency	nvarchar(3)
nOrderId	int
nStatus	int
Source	varchar(64)
bProcessed	bit
fTax	float
fkCountryId	uniqueidentifier
fkPostalServiceId	uniqueidentifier
fkPackagingGroupId	uniqueidentifier
ReferenceNum	varchar(64)
ExternalReference	varchar(64)
PostalTrackingNumber	varchar(64)
CreatedBy	int
CreatedDate	datetime
Address1	nvarchar(255)
Address2	nvarchar(255)
Address3	nvarchar(255)
Town	nvarchar(255)
Region	nvarchar(255)
LifeStatus	int
BuyerPhoneNumber	varchar(64)
Company	nvarchar(255)
SubSource	varchar(128)
AddressVerified	bit
Subtotal	float
PostageCostExTax	float
CountryTaxRate	float
RecalculateTaxRequired	bit
ChannelBuyerName	nvarchar(120)
HoldOrCancel	bit
Marker	tinyint
TotalDiscount	float
fkBankId	uniqueidentifier
FulfillmentLocationId	uniqueidentifier
SecondaryReferenceNum	varchar(25)
PostalServiceCost	float
FulfilmentCenterAcknowledge	bit
PostageDiscount	float
ConversionRate	float

Open_Order
pkOrderID	uniqueidentifier
cFullName	nvarchar(127)
cEmailAddress	nvarchar(255)
cPostCode	nvarchar(127)
dReceievedDate	datetime
dDispatchBy	datetime
fPostageCost	float
fTotalCharge	float
cCurrency	nvarchar(3)
nOrderId	int
nStatus	int
Source	varchar(64)
bProcessed	bit
fTax	float
fkCountryId	uniqueidentifier
fkPostalServiceId	uniqueidentifier
fkPackagingGroupId	uniqueidentifier
ReferenceNum	varchar(64)
ExternalReference	varchar(64)
PostalTrackingNumber	varchar(64)
CreatedBy	int
CreatedDate	datetime
Address1	nvarchar(255)
Address2	nvarchar(255)
Address3	nvarchar(255)
Town	nvarchar(255)
Region	nvarchar(255)
LifeStatus	int
BuyerPhoneNumber	varchar(64)
Company	nvarchar(255)
SubSource	varchar(128)
AddressVerified	bit
Subtotal	float
PostageCostExTax	float
CountryTaxRate	float
RecalculateTaxRequired	bit
ChannelBuyerName	nvarchar(120)
HoldOrCancel	bit
Marker	tinyint
TotalDiscount	float
fkBankId	uniqueidentifier
FulfillmentLocationId	uniqueidentifier
SecondaryReferenceNum	varchar(25)
PostalServiceCost	float
FulfilmentCenterAcknowledge	bit
NotesCount	int
AffectsInOrderBook	bit
IsPickListPrinted	bit
IsRuleRun	bit
ConversionRate	float
StockAllocation	tinyint

Additional order data tables

Order_AdditionalInfo
fkOrderId	uniqueidentifier
BillingName	nvarchar(255)
BillingCompany	nvarchar(255)
BillingAddress1	nvarchar(255)
BillingAddress2	nvarchar(255)
BillingAddress3	nvarchar(255)
BillingTown	nvarchar(255)
BillingRegion	nvarchar(255)
BillingPostcode	nvarchar(255)
BillingCountryName	nvarchar(255)
dPaidOn	datetime
BillingPhoneNumber	nvarchar(255)
CancellationReason	varchar(25)
BillingEmailAddress	nvarchar(255)
DeliveryStartDate	datetime2
DeliveryEndDate	datetime2

OrderXml
rowid	uniqueidentifier
fkOrderId	uniqueidentifier
ObjectXml	xml(-1)
LineTag	varchar(64)
Type	varchar(64)

Order_Refund
pkRefundRowId	uniqueidentifier
fkOrderId	uniqueidentifier
Reason	nvarchar(255)
Amount	decimal
fkBankId	uniqueidentifier
fkOrderItemId	uniqueidentifier
Actioned	bit
AccountingExported	bit
CreateDate	datetime
RefundReference	nvarchar(255)
CancellationQuantity	int
fkOrderItemReturnId	uniqueidentifier
ActionDate	datetime
RefundStatus	nvarchar(32)
ChannelReason	varchar(64)
ChannelReasonSec	varchar(64)
RetryCount	int
IsShippingRefund	bit
IsAdditionalRefund	bit
IsChannelInitiated	bit
SubStatusTag	nvarchar(32)
fkRefundHeaderId	int
RefundedUnit	tinyint
Internal	bit
IgnoredValidation	bit

Accounting_Bank
pkBankId	uniqueidentifier
AccountName	nvarchar(255)
Tags	nvarchar(600)
AutomationType	varchar(64)
CanDelete	bit
bLogicalDelete	bit
Balance	float

ListCountries
pkCountryId	uniqueidentifier
cCountry	nvarchar(64)
cContinent	nvarchar(64)
cCurrency	nvarchar(50)
nPostageCostPerKg	float
cCountryCode	varchar(3)
rowguid	uniqueidentifier
TaxRate	float
bLogicalDelete	bit
AddressFormat	varchar(600)
CustomsRequires	bit

Order packaging and shipping tables

OrderPackaging
fkOrderId	uniqueidentifier
ItemWeight	float
fkPackagingTypeId	uniqueidentifier
PackagingWeight	float
TotalWeight	float
CalcError	nvarchar(255)
ManualAdjust	bit
ErrorState	tinyint
LabelId	varchar(64)
fkPostageFileId	uniqueidentifier
TotalHeight	decimal
TotalWidth	decimal
TotalDepth	decimal
SplitPackageCount	int

PackageGroups
PackageCategoryID	uniqueidentifier
PackageCategory	varchar(64)
bLogicalDelete	bit
rowguid	uniqueidentifier
PreferenceIndex	int

PostalServices
pkPostalServiceId	uniqueidentifier
PostalServiceName	varchar(255)
PostalServiceTag	varchar(2000)
ServiceCountry	varchar(64)
PostalServiceCode	varchar(255)
Vendor	varchar(64)
TrackingNumberRequired	bit
bLogicalDelete	bit
WeightRequired	bit
PrintModule	varchar(600)
PrintModuleTitle	varchar(64)
IgnorePackagingGroup	bit
rowguid	uniqueidentifier
fkShippingAPIConfigId	int
IntegratedServiceId	uniqueidentifier

Order items tables

OrderItem
rowid	uniqueidentifier
fkOrderId	uniqueidentifier
fkStockID	uniqueidentifier
fPricePerUnit	float
nQty	int
ItemNumber	nvarchar(120)
fkLocationId	uniqueidentifier
fkCompositeParentRowId	uniqueidentifier
ChannelSKU	varchar(255)
fkStockItemId_processed	uniqueidentifier
DispatchStockUnitCost	float
ShippingCost	float
SalesTax	float
TaxRate	float
TaxCostInclusive	bit
Cost	float
CostIncTax	float
PartShipped	bit
LineDiscount	float
IsService	bit
ItemSource	varchar(64)
PartShippedQty	int
fkChannelTitleId	int
fkStockItemId	uniqueidentifier
ContainsComposites	bit

Open_OrderItem
rowid	uniqueidentifier
fkOrderId	uniqueidentifier
fPricePerUnit	float
nQty	int
ItemNumber	nvarchar(120)
fkLocationId	uniqueidentifier
fkCompositeParentRowId	uniqueidentifier
ChannelSKU	varchar(255)
fkStockItemId	uniqueidentifier
SalesTax	float
TaxRate	float
TaxCostInclusive	bit
Cost	float
CostIncTax	float
PartShipped	bit
LineDiscount	float
IsService	bit
ItemSource	varchar(64)
PartShippedQty	int
OriginalTitle	nvarchar(1000)
AffectingStockLevel	int
fkStockID	uniqueidentifier
fkImageId	uniqueidentifier

StockItems
pkStockID	uniqueidentifier
cItemNumber	nvarchar(255)
cItemName	nvarchar(1000)
cDescription	nvarchar(-1)
fPricePerItem	float
fkStockControlStockItemId	uniqueidentifier
rowguid	uniqueidentifier

Stock item data tables

StockItem
pkStockItemID	uniqueidentifier
ItemTitle	Nvarchar(512)
ItemNumber	nvarchar(255)
ItemDescription	nvarchar(-1)
CreationDate	datetime
bLogicalDelete	bit
RetailPrice	float
CategoryId	uniqueidentifier
Weight	float
PackageGroup	uniqueidentifier
BinRack	varchar(64)
rowguid	uniqueidentifier
PurchasePrice	float
BarcodeNumber	varchar(255)
DimHeight	float
DimWidth	float
DimDepth	float
ShipperSeparately	bit
TaxRate	float
fkPostalService	uniqueidentifier
bContainsComposites	bit
ModifiedDate	datetime
ModifiedUserName	varchar(64)
ModifyAction	varchar(16)
IsArchived	bit
IsVariationGroup	bit
InventoryTrackingType	tinyint
SerialNumberScanRequired	bit
BatchNumberScanRequired	bit

StockItem_ExtendedProperties
pkRowId	uniqueidentifier
fkStockItemId	uniqueidentifier
ProperyName	nvarchar(50)
ProperyValue	nvarchar(2000)
ProperyType	varchar(50)
UserDefined	bit
fkParentRowId	uniqueidentifier
ModifiedDate	datetime
ModifiedUserName	varchar(64)

ProductCategories
CategoryId	uniqueidentifier
CategoryName	nvarchar(255)

StockLevel
fkStockItemId	uniqueidentifier
fkStockLocationId	uniqueidentifier
Quantity	int
OnOrder	int
CurrentStockValue	float
MinimumLevel	int
AutoAdjust	bit
LastUpdateDate	datetime
LastUpdateOperation	varchar(64)
rowid	uniqueidentifier
PendingUpdate	bit
InOrderBook	int
JIT	bit

StockLocation
pkStockLocationId	uniqueidentifier
Location	nvarchar(255)
Address1	nvarchar(100)
Address2	nvarchar(100)
City	nvarchar(100)
County	nvarchar(64)
Country	nvarchar(64)
ZipCode	nvarchar(24)
bLogicalDelete	bit
IsNotTrackable	bit
LocationTag	varchar(64)
IsFulfillmentCenter	bit
CountInOrderUntilAcknowledgement	bit
FulfilmentCenterDeductStockWhenProcessed	bit
IsWarehouseManaged	bit

ItemLocation
fkStockItemId	uniqueidentifier
fkLocationId	uniqueidentifier
BinRackNumber	nvarchar(64)
rowid	uniqueidentifier

ItemSupplier
fkStockItemId	uniqueidentifier
fkSupplierId	uniqueidentifier
rowid	uniqueidentifier
isDefault	bit
SupplierCode	nvarchar(255)
SupplierCode2	nvarchar(255)
SupplierBarcode	nvarchar(255)
LeadTime	int
KnownPurchasePrice	float
AvgPurchasePrice	float
AvgLeadTime	float
MaxLeadTime	float
MinOrder	int
OnHand	float
MinPurchasePrice	float
MaxPurchasePrice	float
AvgPurchaseQty	float
SupplierMinOrderQty	int
SupplierPackSize	int
LeadTimeVector	varchar(215)

Supplier
pkSupplierID	uniqueidentifier
SupplierName	nvarchar(255)
AddressLine1	nvarchar(255)
AddressLine2	nvarchar(255)
City	nvarchar(100)
County	nvarchar(64)
Country	nvarchar(64)
ZipCode	nvarchar(24)
TelNumber	nvarchar(255)
FaxNumber	nvarchar(255)
eMail	nvarchar(255)
PrimaryWebSite	nvarchar(255)
bLogicalDelete	bit
ContactName	nvarchar(255)
SecondaryTelNumber	nvarchar(255)
Currency	nvarchar(3)

amazon_fba_stock_detail [inventory].[amazon_fba_stock_detail]
pkRowId	int
SubSource	varchar(128)
Region	varchar(5)
MarketplaceId	varchar(128)
SellerId	varchar(128)
SellerSku	varchar(255)
CreateDateTime	datetime2
UpdateDateTime	datetime2
FulfillmentChannelSku	varchar(255)
Asin	varchar(16)
ProductName	varchar(255)
EnrolledInSnl	bit
MarketPlace	varchar(50)
SnlPrice	float
SnlInventory	int
NonSnlInventory	int
Condition	varchar(32)
YourPrice	float
MfnListingExists	bit
MfnFulfillableQuantity	int
AfnListingExists	int
AfnFulfillableQuantity	int
AfnUnsellableQuantity	int
AfnReservedQuantity	int
AfnTotalQuantity	int
PerUnitVolume	float
AfnInboundWorkingQuantity	int
AfnInboundShippedQuantity	int
AfnInboundReceivingQuantity	int

Linnworks Database Structure - Basic

Modified on: Mon, 5 Oct, 2020 at 4:35 PM

More articles in Customisation