CREATE TABLE TransHeader ( 
orden_id INT DEFAULT (0) NOT NULL,
company_id CHAR(2) NOT NULL,
order_no VARCHAR(12) NOT NULL,
order_date SMALLDATETIME NOT NULL,
supplier_no VARCHAR(16) NOT NULL,
cust_no VARCHAR(16) NOT NULL,
acct_no VARCHAR(16) NOT NULL,
carrier VARCHAR(16),
term_id VARCHAR(10) NOT NULL,
trans_id VARCHAR(5),
truck VARCHAR(10),
trailer VARCHAR(10),
driver VARCHAR(16),
doc_no VARCHAR(10),
shipment_no INT DEFAULT (0) NOT NULL,
shipment_co CHAR(2) NOT NULL,
invoice_type VARCHAR(3) DEFAULT ('0') NOT NULL,
invoice_no INT DEFAULT (0) NOT NULL,
invoice_co CHAR(2) NOT NULL,
trans_time SMALLDATETIME,
load_start_time SMALLDATETIME,
load_stop_time SMALLDATETIME,
trans_status INT DEFAULT (0) NOT NULL,
comments VARCHAR(250),
update_time SMALLDATETIME NOT NULL
 CONSTRAINT PK_TransHeader PRIMARY KEY CLUSTERED (orden_id,company_id),
 CONSTRAINT CK_TransHeaderacct_no CHECK ((len([acct_no]) > 0)),
 CONSTRAINT CK_TransHeadercompany_id CHECK ((len([company_id]) > 0)),
 CONSTRAINT CK_TransHeadercust_no CHECK ((len([cust_no]) > 0)),
 CONSTRAINT CK_TransHeaderinvoice_co CHECK ((len([invoice_co]) > 0)),
 CONSTRAINT CK_TransHeaderinvoice_type CHECK ((len([invoice_type]) > 0)),
 CONSTRAINT CK_TransHeaderorder_no CHECK ((len([order_no]) > 0)),
 CONSTRAINT CK_TransHeadershipment_co CHECK ((len([shipment_co]) > 0)),
 CONSTRAINT CK_TransHeadersupplier_no CHECK ((len([supplier_no]) > 0)),
 CONSTRAINT CK_TransHeaderterm_id CHECK ((len([term_id]) > 0)))
CREATE TABLE TransProducts ( 
orden_id INT DEFAULT (0) NOT NULL,
company_id CHAR(2) NOT NULL,
sequence_no INT DEFAULT (0) NOT NULL,
rec_type INT DEFAULT (0) NOT NULL,
prod_id VARCHAR(16) NOT NULL,
prod_name VARCHAR(100) NOT NULL,
gross_gals DECIMAL(14,4) DEFAULT (0) NOT NULL,
net_gals DECIMAL(14,4) DEFAULT (0) NOT NULL,
temperature DECIMAL(14,4) DEFAULT (0) NOT NULL,
temp_uom VARCHAR(3) DEFAULT ('F'),
grav_density DECIMAL(14,4) DEFAULT (0) NOT NULL,
prod_type VARCHAR(3),
tank_no VARCHAR(4),
trans_ref_no VARCHAR(10)
 CONSTRAINT PK_TransProducts PRIMARY KEY CLUSTERED (orden_id,company_id,sequence_no),
 CONSTRAINT CK_TransProductscompany_id CHECK ((len([company_id]) > 0)),
 CONSTRAINT CK_TransProductsprod_id CHECK ((len([prod_id]) > 0)),
 CONSTRAINT CK_TransProductsprod_name CHECK ((len([prod_name]) > 0)))
GO
 CREATE NONCLUSTERED INDEX IX_TransHeaderorder_date ON TransHeader(order_date)
 CREATE NONCLUSTERED INDEX IX_TransHeaderinvoice_no ON TransHeader(invoice_type,invoice_no,invoice_co)
 CREATE NONCLUSTERED INDEX IX_TransHeadershipment_no ON TransHeader(shipment_no,shipment_co)
ALTER TABLE TransProducts ADD 
 CONSTRAINT FK_TransProducts_TransHeader FOREIGN KEY (orden_id,company_id) REFERENCES [dbo].[TransHeader] (orden_id,company_id)
 CREATE NONCLUSTERED INDEX IX_TransProductsprod_id ON TransProducts(prod_id)