-- Table: Customers
CREATE TABLE Customers (
Customer_Id INT PRIMARY KEY,
Customer_Type VARCHAR(50) CHECK (Customer_Type IN ('Business', 'Individual')),
Customer_Name VARCHAR(100) NOT NULL,
Company_Name VARCHAR(100),
Display_Name VARCHAR(100),
Email_Address VARCHAR(100),
Phone_Work VARCHAR(15),
Phone_Mobile VARCHAR(15),
Company_Id INT,
Currency_Id INT,
Tax_Rate_Id INT,
Opening_Balance DECIMAL(10, 2),
Enable_Portal BOOLEAN,
Portal_Language VARCHAR(50),
Document BLOB,
Web_URL VARCHAR(255),
Dept_Id INT,
Designation_Id INT,
Creation_Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
Created_By VARCHAR(50),
Updated_On TIMESTAMP,
Updated_By VARCHAR(50),
Time_Zone VARCHAR(50),
FOREIGN KEY (Company_Id) REFERENCES Company(Company_Id),
FOREIGN KEY (Currency_Id) REFERENCES Currency(Curr_Id),
FOREIGN KEY (Tax_Rate_Id) REFERENCES Tax_Rate(Tax_Rate_Id),
FOREIGN KEY (Dept_Id) REFERENCES Departments(Dept_Id),
FOREIGN KEY (Designation_Id) REFERENCES Designation(Designation_Id)
);
-- Table: Designation
CREATE TABLE Designation (
Designation_Id INT PRIMARY KEY,
Desg_Name VARCHAR(100),
Creation_Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
Created_By VARCHAR(50),
Updated_On TIMESTAMP,
Updated_By VARCHAR(50),
Time_Zone VARCHAR(50)
);
-- Table: Address
CREATE TABLE Address (
Address_Id INT PRIMARY KEY,
Customer_Name VARCHAR(100) NOT NULL,
Country_Region VARCHAR(50),
Address1 VARCHAR(255),
Address2 VARCHAR(255),
City VARCHAR(50),
State VARCHAR(50),
Zip_Code VARCHAR(20),
Phone VARCHAR(15),
Fax_Number VARCHAR(15),
Created_By VARCHAR(50),
Created_On TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
Updated_By VARCHAR(50),
Updated_On TIMESTAMP,
Time_Zone VARCHAR(50),
Customer_Id INT NOT NULL,
FOREIGN KEY (Customer_Id) REFERENCES Customers(Customer_Id)
);
-- Table: Departments
CREATE TABLE Departments (
Dept_Id INT PRIMARY KEY,
Dept_Name VARCHAR(100),
Created_By VARCHAR(50),
Created_On TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
Updated_By VARCHAR(50),
Updated_On TIMESTAMP,
Time_Zone VARCHAR(50),
Customer_Id INT NOT NULL,
FOREIGN KEY (Customer_Id) REFERENCES Customers(Customer_Id)
);
-- Table: Tax Rate
CREATE TABLE Tax_Rate (
Tax_Rate_Id INT PRIMARY KEY,
Tax_Desc VARCHAR(100),
Status VARCHAR(50),
Starting_Date DATE,
End_Date DATE,
Created_By VARCHAR(50),
Created_On TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
Updated_By VARCHAR(50),
Updated_On TIMESTAMP,
Time_Zone VARCHAR(50),
Customer_Id INT,
FOREIGN KEY (Customer_Id) REFERENCES Customers(Customer_Id)
);
-- Table: Currency
CREATE TABLE Currency (
Curr_Id INT PRIMARY KEY,
Curr_Desc VARCHAR(50),
Status VARCHAR(50),
Starting_Date DATE,
End_Date DATE,
Created_By VARCHAR(50),
Created_On TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
Updated_By VARCHAR(50),
Updated_On TIMESTAMP,
Time_Zone VARCHAR(50)
);
-- Table: Company
CREATE TABLE Company (
Company_Id INT PRIMARY KEY,
Company_Desc VARCHAR(255),
Status VARCHAR(50),
Created_By VARCHAR(50),
Created_On TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
Updated_By VARCHAR(50),
Updated_On TIMESTAMP,
Time_Zone VARCHAR(50)
);
-- Table: Item Master
CREATE TABLE Item_Master (
ITEM_ID INT PRIMARY KEY,
ITEM_NAME VARCHAR(100) NOT NULL,
ITEM_PRICE DECIMAL(10, 2),
ITEM_COST DECIMAL(10, 2),
ITEM_DESCRIPTION VARCHAR(255),
PACKING_UNIT VARCHAR(50),
ITEMS_IN_PACK INT,
CREATED_BY VARCHAR(50),
CREATED_ON TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UPDATED_BY VARCHAR(50),
UPDATED_ON TIMESTAMP,
ITEM_STATUS VARCHAR(50),
ITEM_TYPE VARCHAR(50),
TIME_ZONE VARCHAR(50)
);
-- Table: Estimation Master
CREATE TABLE Estimation_Master (
ESTIMATION_ID INT PRIMARY KEY,
CUSTOMER_ID INT NOT NULL,
TOTAL_BILL DECIMAL(10, 2),
DISCOUNT DECIMAL(10, 2),
TAX_ID INT,
NET_BILL DECIMAL(10, 2),
INVOICE_DATE DATE,
CREATED_BY VARCHAR(50),
UPDATED_BY VARCHAR(50),
UPDATED_ON TIMESTAMP,
STATUS VARCHAR(50),
TIME_ZONE VARCHAR(50),
FOREIGN KEY (CUSTOMER_ID) REFERENCES Customers(Customer_Id),
FOREIGN KEY (TAX_ID) REFERENCES Tax_Rate(Tax_Rate_Id)
);
-- Table: Estimation Detail
CREATE TABLE Estimation_Detail (
DET_ID INT PRIMARY KEY,
ESTIMATION_ID INT NOT NULL,
ITEM_ID INT NOT NULL,
PRICE DECIMAL(10, 2),
QTY INT,
TOTAL_AMOUNT DECIMAL(10, 2),
TIME_ZONE VARCHAR(50),
FOREIGN KEY (ESTIMATION_ID) REFERENCES Estimation_Master(ESTIMATION_ID),
FOREIGN KEY (ITEM_ID) REFERENCES Item_Master(ITEM_ID)
);
-- Table: Invoice Master
CREATE TABLE Invoice_Master (
INVOICE_ID INT PRIMARY KEY,
ESTIMATION_ID INT NOT NULL,
CUSTOMER_ID INT NOT NULL,
TOTAL_BILL DECIMAL(10, 2),
DISCOUNT DECIMAL(10, 2),
TAX_ID INT,
NET_BILL DECIMAL(10, 2),
INVOICE_DATE DATE,
CREATED_BY VARCHAR(50),
UPDATED_BY VARCHAR(50),
UPDATED_ON TIMESTAMP,
STATUS VARCHAR(50),
TIME_ZONE VARCHAR(50),
FOREIGN KEY (ESTIMATION_ID) REFERENCES Estimation_Master(ESTIMATION_ID),
FOREIGN KEY (CUSTOMER_ID) REFERENCES Customers(Customer_Id),
FOREIGN KEY (TAX_ID) REFERENCES Tax_Rate(Tax_Rate_Id)
);
-- Table: Invoice Detail
CREATE TABLE Invoice_Detail (
DET_ID INT PRIMARY KEY,
INVOICE_ID INT NOT NULL,
ITEM_ID INT NOT NULL,
PRICE DECIMAL(10, 2),
QTY INT,
TOTAL_AMOUNT DECIMAL(10, 2),
TIME_ZONE VARCHAR(50),
FOREIGN KEY (INVOICE_ID) REFERENCES Invoice_Master(INVOICE_ID),
FOREIGN KEY (ITEM_ID) REFERENCES Item_Master(ITEM_ID)
);