Supplier data model for Inventory Management database design

Overview

The Logical Data Model of the SUPPLIER Subject Area includes the following entities:

• Entity: SUPPLIER
• Entity: SUPPLIER_TAX_TYPE
• Entity: SUPPLIER_ITEM_TAX_TYPE

Entity: SUPPLIER

List of Store Suppliers.

Attributes
ID	PRIMARY KEY
Store_ID	Lookup for the Store for which the Supplier was created. NOT NULL
City_ID	Lookup for the City where the Supplier is located. NOT NULL
Code	Supplier code. NOT NULL UNIQUE
Phone	Supplier phone number. NOT NULL
First_Name	Supplier First Name NOT NULL
Last_Name	Supplier Last Name NOT NULL
Is_Company	Flag attribute to indicate if the Supplier is a company. NOT NULL
Company_Name	Company Name. If Is_Company is TRUE then Company_Name is mandatory
Tax_Number	Supplier Tax Number
Is_Tax_Exempted	Flag attribute to indicate if the Supplier is tax exempted. NOT NULL
Billing_Address	Supplier Billing Address. NOT NULL.
Postal_Code	Supplier address Postal Code.
Email	Supplier email address. NOT NULL.
Created_Emp_Login_ID	Lookup for the Store Employee who created the Supplier record.
Created_Time	Date and Time when the Supplier record was created. NOT NULL
Is_Active	Flag attribute to indicate if the Supplier is still operational or is suspended. NOT NULL
Comments	Internal notes used for clarifications on record content.

Entity: SUPPLIER_TAX_TYPE

List of Tax types applied by a Supplier.

Attributes
ID	PRIMARY KEY
Supplier_ID	Lookup for the Supplier for which the Tax Type record was defined. NOT NULL
Name	Tax Type name. Example: 'Value Added Tax' etc. NOT NULL
Code	Tax Type code. Example: 'VAT' etc. NOT NULL
Description	Tax Type description
Is_Percentage	Flag to indicate if the tax value is a percentage or a flat amount. Example: ⦁ If Is_Percentage is TRUE and Value is '40' then it means a 40% Tax ⦁ If Is_Percentage is FALSE and Value is '40' then it means 40 money amount expressed in Store default currency. See Entity: STORE, Attribute: Currency_ID for more information. NOT NULL
Value	Value of Tax. Example: ⦁ If Is_Percentage is TRUE then the Value is the Tax percentage. For example a value of '14.50' means 14.50% Tax. ⦁ If Is_Percentage is FALSE then the Value is the money amount fix Tax. For example a Tax Value of '14.50' means a Tax of 14.50 expressed in Store default currency. See Entity: STORE, Attribute: Currency_ID for more information. NOT NULL
Created_Time	Timestamp when the Tax type record was created. NOT NULL
Start_Time	The start timestamp from which the Tax type is applicable. NOT NULL
End_Time	The end timestamp after which the Tax type is not applicable. When the End_Time is NULL the Tax type is still applicable.
Is_Active	Flag attribute to indicate if the Tax_Type is still active. NOT NULL
Comments	Internal notes used for clarifications on record content.

Entity: SUPPLIER_ITEM_TAX_TYPE

List of all Tax Types applied by a Supplier to an Item. A Supplier can apply many Tax Types to an Item.

Attributes
ID	PRIMARY KEY
Item_ID	Lookup for the Item to which the Supplier Tax Type was applied. NOT NULL
Supplier_Tax_Type_ID	Lookup for the Supplier Tax Type applied to the Item. NOT NULL
Created_Time	Timestamp when the Supplier Tax Type was assigned to Item. NOT NULL
Start_Time	The start timestamp from which the Supplier Tax Type is applicable. NOT NULL
End_Time	The end timestamp after which the Supplier Tax Type is not applicable. When the End_Time is NULL the Supplier Tax Type is still applicable.
Description	Description of the Supplier Tax Type applied to the Item.