pawelpuszka.github.io

COMPUTER STORE DATABASE SCHEMA

Designed by Paweł Puszka

PAYMENT_METHODS * P payment_method_ID SMALLINT * U payment_method_name NVARCHAR2 (50) PaymentMethods_PK (payment_method_ID) PaymentMethods__UN (payment_method_name) PRODUCTS * P product_ID INTEGER F category_ID SMALLINT * product_name NVARCHAR2 (100) serial_number NVARCHAR2 (50) unit_price NUMBER (8,2) PRODUCTS_PK (product_ID) Products_ProductCategories_FK (category_ID) RECEIPT_PRODUCTS_LISTS * P receipt_list_ID INTEGER * PF receipt_ID INTEGER * F product_ID INTEGER purchased_product_qty SMALLINT PurchasedProductsList_PK (receipt_list_ID, receipt_ID) RECEIPT_PRODUCTS_LISTS__UN (receipt_ID) PurchasedList_Products_FK (product_ID) RECEIPT_PRODUCTS_LISTS_RECEIPTS_FK (receipt_ID) ORDERS * P order_ID INTEGER U order_nr NVARCHAR2 (20) * F supplier_ID SMALLINT order_send_date DATE Orders_PK (order_ID) ORDERS__UN (order_nr) Orders_Suppliers_FK (supplier_ID) ONLINE_STOREHOUSE * PF product_ID INTEGER * online_product_qty INTEGER deficit_alert SMALLINT StationaryStoreHousev1_PK (product_ID) OnlineStoreHouse_Products_FK (product_ID) PAY_SCALES * PF position_ID SMALLINT * min_wages NUMBER (8,2) * max_wages NUMBER (8,2) PayScales_PK (position_ID) PayScales_EmployeePositions_FK (position_ID) ORDERED_PRODUCTS_LISTS * PF order_ID INTEGER * PF product_ID INTEGER stationary_store_qty SMALLINT online_store_qty SMALLINT OrderedProductsList_PK (order_ID, product_ID) OrderedProdList_Orders_FK (order_ID) OrderedProdList_Products_FK (product_ID) RECEIPTS * P receipt_ID INTEGER * U receipt_no NVARCHAR2 (20) * UF transaction_ID INTEGER F payment_term_id INTEGER Receipts_PK (receipt_ID) Receipts_receipt_no_UN (receipt_no ) RECEIPTS_transaction_UN (transaction_ID) RECEIPTS_TRANSACTIONS_FK (transaction_ID) RECEIPTS_PAYMENT_TERMS_FK (payment_term_id) INVOICE_PRODUCTS_LISTS * P invoice_list_ID INTEGER * PF income_invoice_ID INTEGER * F product_ID INTEGER purchased_product_qty SMALLINT INVOICE_PRODUCTS_LISTS_PK (invoice_list_ID, income_invoice_ID) INVOICE_PRODUCTS_LISTS__UN (income_invoice_ID) INVOICE_PRODUCTS_LISTS__FK (product_ID) INVOICE_LISTS_INCOME__FK (income_invoice_ID) STATIONARY_STOREHOUSE * PF product_ID INTEGER * product_quantity INTEGER deficit_alert SMALLINT StoreHouse_PK (product_ID) StationStoreHouse_Products_FK (product_ID) SUPPLIES * P supply_ID INTEGER * order_nr NVARCHAR2 (20) * F cost_invoice_ID INTEGER * delivery_date DATE SUPPLIERS_PK (supply_ID) SUPPLIES_COST_INVOICES_FK (cost_invoice_ID) PAYMENT_TERMS * P term_id INTEGER term_name VARCHAR2 (50 CHAR) * days_to_payment INTEGER PAYMENT_TERMS_PK (term_id) SUPPLIERS * P supplier_ID SMALLINT * supplier_name NVARCHAR2 (100) F address_ID INTEGER * nip NVARCHAR2 (10) SUPPLIERS_PKv2 (supplier_ID) SUPPLIERS_ADRESSES_FK (address_ID) SECTIONS * P section_ID SMALLINT * U section_name VARCHAR2 (100 CHAR) Sections_PK (section_ID) Sections__UN (section_name) PRODUCT_CATEGORIES * P category_ID SMALLINT * U category_name NVARCHAR2 (30) ProductCategories_PK (category_ID) ProductCategories__UN (category_name) TRANSACTION_STATUSES * P status_ID SMALLINT U status_name NVARCHAR2 (30) TransactionStatus_PK (status_ID) TransactionStatus__UN (status_name) CLIENTS_LOYALTY_CARDS * P loyalty_card_id INTEGER loyalty_card_label VARCHAR2 (15 CHAR) LOYALTY_CARD_PK (loyalty_card_id) WHOLESALE_CLIENTS * P wholesale_client_ID INTEGER * wholesale_client_name NVARCHAR2 (100) F loyalty_card_id INTEGER F address_ID INTEGER * U nip VARCHAR2 (10 CHAR) U regon VARCHAR2 (9 BYTE) email VARCHAR2 (50 CHAR) CLIENTS_PK (wholesale_client_ID) WHOLESALE_CLIENTS_REGON_UN (regon) WHOLESALE_CLIENTS_NIP_UN (nip) WHOLESALE_CLIENTS_CARDS_FK (loyalty_card_id) WHOLESALE_CLIENTS_ADDRESSES_FK (address_ID) SUPPLIED_PRODUCTS_LISTS * PF supply_ID INTEGER * PF product_ID INTEGER product_net_price NUMBER (10,2) tax NUMBER (3,2) quantity SMALLINT SuppliedProductList_PK (supply_ID, product_ID) SUPP_PROD_LISTS_SUPPLIES_UN (supply_ID) SuppliedProdList_Supplies_FK (supply_ID) SuppliedProdList_Products_FK (product_ID) COST_INVOICES * P cost_invoice_ID INTEGER * U cost_invoice_nr NVARCHAR2 (30) * F supplier_ID SMALLINT tota_net_price NUMBER (12,2) total_tax NUMBER (12,2) is_paid CHAR (1) cost_invoice_date DATE CostInvoices_PK (cost_invoice_ID) CostInvoices__UN (cost_invoice_nr) CostInvoices_Suppliers_FK (supplier_ID) EMPLOYEE_POSITIONS * P position_ID SMALLINT * U position_name NVARCHAR2 (40) EmployeePositions_PK (position_ID) EmployeePositions__UN (position_name) TRANSACTIONS * P transaction_ID INTEGER F employee_ID SMALLINT * F payment_method_ID SMALLINT * F delivery_method_ID SMALLINT * F status_ID SMALLINT start_time TIMESTAMP end_time TIMESTAMP Transactions_PK (transaction_ID) TRANSACTIONS_RECEIPTS_FKv1 (employee_ID) Transactions_PaymentMethods_FK (payment_method_ID) Transactions_TranStatus_FK (status_ID) TRANSACTIONS_DELIVERY_METHODS_FK (delivery_method_ID) EMPLOYEES * P employee_ID SMALLINT * employee_name NVARCHAR2 (20) * employee_surname NVARCHAR2 (30) pesel NVARCHAR2 (11) email VARCHAR2 (50 CHAR) * F address_ID INTEGER * UF contract_ID SMALLINT EMPLOYEES_PK (employee_ID) EMPLOYEES__UN (contract_ID) Employees_Adresses_FK (address_ID) Employees_EmployeeContracts_FK (contract_ID) EMPLOYEES_CONTRACTS * P contract_ID SMALLINT wages NUMBER (8,2) F section_ID SMALLINT F position_ID SMALLINT * hire_date DATE end_date DATE EmployeeContracts_PK (contract_ID) EmployeeContracts_Sections_FK (section_ID) EmpContracts_EmpPositions_FK (position_ID) DELIVERY_METHODS * P delivery_method_ID SMALLINT delivery_method_name SMALLINT Sales_PK (delivery_method_ID) INCOME_INVOICES * P income_invoice_ID INTEGER * U income_invoice_no NVARCHAR2 (20) * UF transaction_id INTEGER * F wholesale_client_ID INTEGER * F payment_term_id INTEGER Invoices_PK (income_invoice_ID) IncomeInvoices_invoice_UN (income_invoice_no) IncomeInvoices_transaction_UN (transaction_id) InInvoices_WholesaleClients_FK (wholesale_client_ID) INCOME_INVOICES_TRANSACTIONS_FK (transaction_id) INCOME_INVOICES_PAYMENT_TERMS_FK (payment_term_id) ADDRESSES * P address_ID INTEGER street NVARCHAR2 (100) city NVARCHAR2 (50) postal_code CHAR (5 CHAR) phone_number VARCHAR2 (11 CHAR) ADRESSES_PK (address_ID)