NetSuite Internals – The 3 Main Tables Transactions, Items, Entities

NetSuite Internals: Deep Dive into the Three Core Tables

NetSuite’s data structure revolves around three primary categories of tables:

1. Entities – Customers, Vendors, Employees, and Contacts
2. Items – Products, Services, and Inventory
3. Transactions – Sales Orders, Invoices, Payments, etc.

These tables do not exist as single physical tables but are logical groupings of multiple interconnected tables. Let’s explore each area in depth.

---

1️⃣ Entities (Customers, Vendors, Employees, Contacts)

Definition

The Entities category in NetSuite represents people and organizations involved in business operations. This includes:

• Customers
• Vendors
• Employees
• Contacts
• Partners

Key Tables in Entities

Table Name	Description
Customer	Stores information about customers (B2B & B2C).
Vendor	Stores supplier and vendor details.
Employee	Stores internal employee records.
Contact	Individual contacts associated with Customers, Vendors, or Partners.
Partner	Stores partner records for channel sales and referral programs.
Lead & Prospect	Potential customers before becoming Customer.
Subsidiary	Stores company divisions for multi-subsidiary setups.
Department	Business departments (e.g., Sales, HR, Finance).
Class	Classification for transactions and reporting.
Location	Physical locations related to business operations.

Entity Relationships

Entities are often linked to transactions, roles, and classification structures.

Example: Customer and Transactions Relationship

A Customer is connected to:

• Transaction (Sales Orders, Invoices, Payments)
• Item (Products the customer buys)
• Employee (Sales Rep handling the customer)

🔹 SQL Example: Fetching Customers and Their Transactions

SELECT 
    c.id AS Customer_ID, 
    c.entityid AS Customer_Name, 
    t.id AS Transaction_ID, 
    t.trandate AS Transaction_Date, 
    t.amount AS Transaction_Amount
FROM 
    Customer c
JOIN 
    Transaction t ON c.id = t.entity
WHERE 
    t.type IN ('SalesOrd', 'Invoice')
ORDER BY 
    t.trandate DESC;

---

2️⃣ Items (Products, Services, Inventory)

Definition

The Items category represents products and services that a company sells, purchases, or tracks in inventory.

Key Tables in Items

Table Name	Description
Item	Master table for all item types.
Inventory_Item	Stores physical stock-tracked items.
Non_Inventory_Item	Items not tracked in inventory (e.g., labor, services).
Service_Item	Stores service-based offerings (e.g., consulting).
Assembly_Item	Used for manufacturing and assembly (BOM).
Kit_Item	Pre-defined bundles of multiple items.
Lot_Numbered_Item	Tracks batch-based inventory with lot numbers.
Serialized_Item	Tracks inventory with unique serial numbers.
Gift_Certificate_Item	Manages gift cards and stored-value items.
Pricing	Stores item-specific pricing rules (by customer, currency, etc.).
Item_Location	Tracks item availability by location.

Item Relationships

• Items link to transactions via Transaction_Line.
• They are classified under Item Categories, Pricing, and Inventory Locations.

🔹 SQL Example: Fetching Item Sales Data

SELECT 
    i.itemid AS Item_Name, 
    SUM(tl.quantity) AS Total_Quantity_Sold, 
    SUM(tl.amount) AS Total_Sales_Amount
FROM 
    Transaction_Line tl
JOIN 
    Transaction t ON tl.transaction = t.id
JOIN 
    Item i ON tl.item = i.id
WHERE 
    t.type = 'Invoice'
GROUP BY 
    i.itemid
ORDER BY 
    Total_Sales_Amount DESC;

---

3️⃣ Transactions (Orders, Payments, Bills, Journals)

Definition

The Transactions category represents the financial and operational events within the system, such as:

• Sales Orders
• Invoices
• Purchase Orders
• Payments
• Expenses
• Journal Entries

Key Tables in Transactions

Table Name	Description
Transaction	Master table for all transactions.
Transaction_Line	Stores line items related to each transaction.
Sales_Order	Stores customer orders before fulfillment.
Invoice	Stores billed amounts for sales.
Cash_Sale	Sales completed immediately with payment.
Customer_Payment	Records customer payments.
Credit_Memo	Used for refunds and credits.
Bill	Stores vendor invoices for payment.
Vendor_Payment	Records payments made to vendors.
Journal_Entry	Stores manual accounting adjustments.
Expense_Report	Stores employee expenses for reimbursement.

Transaction Relationships

Transactions have multiple dependencies:

• Entities (Customers, Vendors, Employees) interact with transactions.
• Items are purchased, sold, or adjusted within transactions.
• GL Accounts link transactions to financial records.

🔹 SQL Example: Fetching Sales Transactions and Customers

SELECT 
    t.id AS Transaction_ID,
    t.tranid AS Transaction_Number,
    t.trandate AS Transaction_Date,
    t.type AS Transaction_Type,
    c.entityid AS Customer_Name,
    SUM(tl.amount) AS Total_Amount
FROM 
    Transaction t
JOIN 
    Transaction_Line tl ON t.id = tl.transaction
JOIN 
    Customer c ON t.entity = c.id
WHERE 
    t.type IN ('SalesOrd', 'Invoice')
GROUP BY 
    t.id, c.entityid
ORDER BY 
    t.trandate DESC;

---

4️⃣ How the Three Categories Work Together

These three categories are interdependent, forming a structured data model:

• Entities (Customers, Vendors, Employees) interact with Transactions (Sales Orders, Invoices, Bills).
• Transactions include Items (Products, Services, Inventory).
• Transactions generate financial records (Journal Entries).

Example: Sales Order Flow

1️⃣ A Customer places an order → (Customer Table)
2️⃣ A Sales Order is created → (Transaction Table)
3️⃣ The order contains line items → (Transaction_Line Table)
4️⃣ Items are fetched from Inventory → (Item Table)
5️⃣ Upon fulfillment, an Invoice is generated → (Transaction Table)
6️⃣ The customer makes a Payment → (Customer_Payment Table)
7️⃣ Financial records are updated in the General Ledger.

---

5️⃣ Best Practices for Working with NetSuite Tables

✔ Use Joins Efficiently – Minimize table joins to avoid performance issues.
✔ Use Indexed Fields – Query using indexed fields like id, entity, trandate.
✔ Limit Data for Performance – Avoid selecting unnecessary columns in SQL/SuiteQL queries.
✔ Use Saved Searches for Reports – If not using SuiteQL, leverage NetSuite’s Saved Searches for reporting.