Warehouse
warehouse_id
uuid
NN
web_app_customer
uuid
NN
warehouse_dba
varchar
warehouse_name_listed
varchar
NN
warehouse_location
varchar
NN
online_inventory_website
varchar
online_inventory_username
varchar
online_inventory_password
varchar
qbs_warehouse_location_id
varchar
address1
varchar
NN
address2
varchar
city
varchar
NN
state
varchar
country
integer
NN
zipcode
varchar
NN
billing_address1
varchar
billing_address2
varchar
billing_address_city
varchar
billing_address_state
varchar
billing_address_country
integer
billing_address_zipcode
varchar
orders_email1
character varying
orders_email2
character varying
orders_fax1
integer
orders_wechat1
character varying
orders_whatsapp1
character varying
orders_other1
character varying
receiving_contact1
varchar
receiving_contact2
varchar
receiving_contact3
varchar
receiving_email1
varchar
receiving_email2
varchar
receiving_email3
varchar
receiving_fax1
varchar
receiving_messaging1
varchar
receiving_messaging2
varchar
main_contact
varchar
main_contact_phone
integer
main_contact_phone_ext
integer
main_contact_cell
integer
main_contact_sms
boolean
main_contact_email
varchar
customer_service_phone
integer
customer_service_phone_ext
integer
allow_same_day_orders
boolean
billable
boolean
timezone
integer(2)
NN
warehouse_open_time
time
warehouse_close_time
time
shipping_open_time
time
shipping_close_time
time
receiving_open_time
time
receiving_close_time
time
friday_shipping_open_time
time
friday_shipping_closed_time
time
friday_open_time
time
friday_close_time
time
open_sunday
time
open_monday
time
open_tuesday
time
open_thursday
time
open_friday
time
open_saturday
time
repack_capabilities
varchar
dry_ice
varchar
pallet_charge_a
integer
pallet_charge_b
integer
lumping_fee
integer
ctr_unloading_fee
integer
damage_control
boolean
own_pallet_with_product
varchar
pallet_exchange
boolean
rate_sheet
varchar
warehouse_holidays
varchar
List of warehouses and Cold Storages
Customers
customer_id
uuid
NN
web_app_customer
uuid
NN
customer_name
varchar(41)
NN
customer_dba
varchar(41)
customer_name_listed_as
varchar(41)
used_to_be_called
varchar(41)
used_to_be_called_date_change
date
salesman
integer
quickbooks_cust_id
varchar
erp_custom_id
varchar
customer_type_id
integer
NN
order_frequency
integer
order_frequency_days
integer
avg_order_day
varchar
buyer1_position
varchar(50)
buyer1_firstname
varchar(50)
buyer1_lastname
varchar(50)
buyer1_email
varchar(100)
buyer1_phone
varchar(21)
buyer1_phone_ext
varchar(10)
buyer1_phone_direct
varchar(21)
buyer1_mobile
varchar(21)
buyer1_mobile_sms
boolean
buyer1_wechat
varchar(50)
buyer1_whatsapp
varchar(50)
buyer2_position
varchar(50)
buyer2_firstname
varchar(50)
buyer2_lastname
varchar(50)
buyer2_email
varchar(100)
buyer2_phone
varchar(21)
buyer2_phone_ext
varchar(10)
buyer2_phone_direct
varchar(21)
buyer2_mobile
varchar(21)
buyer2_mobile_sms
boolean
buyer2_wechat
varchar(50)
buyer2_whatsapp
varchar(50)
buyer3_position
varchar(50)
buyer3_firstname
varchar(50)
buyer3_lastname
varchar(50)
buyer3_email
varchar(100)
buyer3_phone
varchar(21)
buyer3_phone_ext
varchar(10)
buyer3_phone_direct
varchar(21)
buyer3_mobile
varchar(21)
buyer3_mobile_sms
boolean
buyer3_wechat
varchar(50)
buyer3_whatsapp
varchar(50)
buyer4_position
varchar(50)
buyer4_firstname
varchar(50)
buyer4_lastname
varchar(50)
buyer4_email
varchar(100)
buyer4_phone
varchar(21)
buyer4_phone_ext
varchar(10)
buyer4_phone_direct
varchar(21)
buyer4_mobile
varchar(21)
buyer4_mobile_sms
boolean
buyer4_wechat
varchar(50)
buyer4_whatsapp
varchar(50)
buyer5_position
varchar(50)
buyer5_firstname
varchar(50)
buyer5_lastname
varchar(50)
buyer5_email
varchar(100)
buyer5_phone
varchar(21)
buyer5_phone_ext
varchar(10)
buyer5_phone_direct
varchar(21)
buyer5_mobile
varchar(21)
buyer5_mobile_sms
boolean
buyer5_wechat
varchar(50)
buyer5_whatsapp
varchar(50)
address1
varchar(60)
address2
varchar(60)
city
varchar(57)
state
varchar(50)
country_id
integer(3)
zipcode
varchar(10)
billing_company_name
varchar(60)
billing_address1
varchar(60)
billing_address2
varchar(60)
billing_city
varchar(57)
billing_state
varchar(50)
billing_country_id
integer(3)
billing_zipcode
varchar(8)
delivery1_label
varchar(40)
delivery1_attn
varchar(40)
delivery1_name
varchar(40)
delivery1_address1
varchar(60)
delivery1_address2
varchar(60)
delivery1_address_city
varchar(57)
delivery1_address_state
varchar(50)
delivery1_address_country_id
integer(3)
delivery1_address_zipcode
varchar(8)
delivery1_address_phone
varchar(21)
delivery1_address_email
varchar(100)
delivery1_address_attach
varchar
delivery1_address_website
text
delivery1_address_instructions
varchar(300)
delivery2_label
varchar(40)
delivery2_attn
varchar(40)
delivery2_name
varchar(40)
delivery2_address1
varchar(60)
delivery2_address2
varchar(60)
delivery2_address_city
varchar(57)
delivery2_address_state
varchar(50)
delivery2_address_country_id
integer(3)
delivery2_address_zipcode
varchar(8)
delivery2_address_phone
varchar(21)
delivery2_address_email
varchar(100)
delivery2_address_attach
varchar
delivery2_address_website
text
delivery2_address_instructions
varchar(300)
delivery3_label
varchar(40)
delivery3_attn
varchar(40)
delivery3_name
varchar(40)
deliveru3_address1
varchar(60)
delivery3_address2
varchar(60)
delivery3_address_city
varchar(57)
delivery3_address_state
varchar(50)
delivery3_address_country_id
integer(3)
delivery3_address_zipcode
varchar(8)
delivery3_address_phone
varchar(21)
delivery3_address_email
varchar(100)
delivery3_address_attach
varchar
delivery3_address_website
text
delivery3_address_instructions
varchar(300)
delivery4_label
varchar(40)
delivery4_attn
varchar(40)
delivery4_name
varchar(40)
delivery4_address1
varchar(60)
delivery4_address2
varchar(60)
delivery4_address_city
varchar(57)
delivery4_address_state
varchar(50)
delivery4_address_country_id
integer(3)
delivery4_address_zipcode
integer(8)
delivery4_address_phone
varchar(21)
delivery4_address_email
varchar(100)
delivery4_address_attach
varchar
delivery4_address_website
text
delivery4_address_instructions
varchar(300)
accounting1_contact_name
varchar(50)
accounting1_contact_phone
varchar(21)
accounting1_contact_phone_ext
varchar(10)
accounting1_contact_email
varchar(100)
accounting2_contact_name
varchar(50)
accounting2_contact_phone
varchar(21)
accounting2_contact_phone_ext
varchar(10)
accounting2_contact_email
varchar(100)
accounting3_contact_name
varchar(50)
accounting3_contact_phone
varchar(21)
accounting3_contact_phone_ext
varchar(10)
accounting3_contact_email
varchar(100)
send_invoice_mail_id
boolean
send_invoice_email_id
boolean
send_order_confirm
boolean
order_confirm_by_email
boolean
order_confirm_by_sms
boolean
order_confirm_by_wechat
boolean
order_confirm_by_whatsapp
boolean
order_confirm_buyer
boolean
bill_by_dba
boolean
vendor_number
varchar(25)
credit_insured
boolean
credit_limit
integer(15)
payment_terms
integer(2)
payment_type
integer(2)
average_pay_time
integer(3)
current_balance_owed
integer(18)
sales_order_default
integer(1)
order_shipping_vendor_default_id
integer(1)
order_shipping_collect_default
integer(1)
email_sending_category_id
integer(3)
Since ANY and ALL Sales data is listed under the Sales table, it is accessable via the Customer UUID and UUID of the Sale. Therefore, anything that is associated on a separate table will be JOINed together on the frontend.
Routing seems hectic, but it makes sense database wise.
Orders - COME BACK TO! NEED MORE WORK!
order_id
uuid
NN
web_app_customer
uuid
NN
customer_uuid
uuid
NN
customer_billing_city
uuid
NN
customer_po_number
varchar(25)
NN
salesman_entered_order
integer
NN
warehouse_id
uuid
NN
order_type_id
integer(2)
NN
order_rush
integer(1)
order_number
varchar
NN
order_sent_on
timestamptz
NN
trucker_id
uuid
ship_term
integer
order_first_created_date
timestamptz
NN
customer_order_confirmation
timestamptz
special_instructions_note
varchar(300)
inventory_product_id
uuid
NN
inventory_lot_number
varchar
NN
product_quantity
integer
NN
This is the barebones table. It will utilize IDs to make it quick!
ANYTHING that is listed in another table can be pulled up from just using the ID from another table and joining the details. Crazy!
This means I don't add the following: customer billing city and state,
Salesman
salesman_id
integer
NN
web_app_customer
uuid
NN
salesman_username
varchar
NN
salesman_password
varchar
NN
salesman_role_id
integer
NN
salesman_abbrev
varchar(3)
NN
salesman_work_phone
varchar(21)
salesman_work_phone_ext
varchar(10)
salesman_mobile
varchar(21)
salesman_fax
varchar(21)
salesman_email
varchar(100)
NN
salesman_wechat
varchar(50)
salesman_whatsapp
varchar(50)
Web_App_Customer
web_app_customer_id
uuid
NN
This is the master list of the web app customers. Company IDs are assigned in here and those are the UUIDs
Order_Type
order_type_id
integer
NN
order_type_name
varchar(30)
Vendors
vendor_id
uuid
NN
web_app_customer
uuid
NN
Roles
role_id
integer
NN
role_name
varchar(30)
NN
role_permission
integer
NN
Customer_Type
customer_type_id
integer
NN
customer_type_name
varchar
sellable
varchar
Think this will be a table, but not fully sure. Still processing and thinking things through. Same as Sellable table...
Sellable
sellable_id
integer
NN
sellable_type
varchar(50)
More thought needs to go into this! Currently added, but more thought out is needed!
Payment_terms
payment_terms_id
integer
NN
payment_days
integer
NN
payment_name
integer
NN
Need to think this one through more as well!!
Payment_type
payment_type_id
integer
NN
payment_type_name
varchar(50)
NN
Ship_terms
ship_terms_id
integer
NN
ship_terms_name
varchar(20)
NN
simply lists it on the order. Not for us, but for them. But, if Pre-Paid, it automatically makes it a custom Trucking PO we make.
Languages**
languages_id
uuid
NN
language_name
varchar(30)
NN
abbrev
varchar(3)
NN
abbrev_alias
varchar(3)
NN
language_orientation
varchar(3)
NN
Still need to figure out how to build this table to be able to use it. Might have to figure out how to list the area and the association. Will need to ask ChatGPT about this I feel... It will be for relabeling titles and stuff in the foreign language selected.
Inventory***
inventory_id
uuid
NN
web_app_customer
uuid
NN
product_id
uuid
NN
purchase_order_id
uuid
NN
warehouse_id
uuid
NN
warehouse_lot
varchar
NN
product_quantity
integer
on_special
boolean
case_per pallet
integer
case_ti
integer
case_hi
integer
list_price
integer
NN
cost_price
integer
NN
anniversary_date
date
NN
comments
text
Need Products made before actually adding this!
Products***
products_id
uuid
NN
web_app_customer
uuid
NN
product_sku
varchar(40)
NN
quickbooks_product_id
integer
product_size
varchar
product_family
varchar
NN
product_short_description
varchar
NN
product_full_description
varchar
pack_size
varchar
product_origin_id
integer
NN
upc_outer
integer
upc_inner
integer
partials_per_case
integer
NN
case_net_weight
integer
NN
case_gross_weight
integer
NN
unit_of_measure
varchar(4)
NN
bill_by
integer(2)
NN
catch_method
integer
NN
latin_name
varchar
special_import_requirements
varchar
dimensions
varchar
average_lead_time
integer
reorder_alert
integer
special_import_requirements
varchar
harvest_season_start1
integer
harvest_season_end1
integer
harvest_season_start2
integer
harvest_season_end2
integer
harvest_season_start3
integer
harvest_season_end3
integer
harvest_season_start4
integer
harvest_season_end4
integer
sell_season_start1
integer
sell_season_end1
integer
sell_season_start2
integer
sell_season_end2
integer
sell_season_holiday
varchar
sell_season_weather1
varchar
sell_season_weather2
varchar
sell_season_weather3
varchar
sell_season_weather4
varchar
upc_outercase
varchar
upc_innercase
varchar
dimensions
varchar
product_pic1
text
product_pic2
text
product_pic3
text
product_pic4
text
product_pic5
text
product_pic6
text
product_pic7
text
product_pic8
text
product_pic9
text
product_pic10
text
product_pic11
text
product_pic12
text
product_pic13
text
product_pic14
text
product_pic15
text
product_spec_sheet1
text
product_spec_sheet2
text
product_spec_sheet3
text
product_spec_sheet4
text
product_spec_sheet5
text
cooking_instructions1
text
cooking_instructions2
text
An idea with this is to be able to build a spec sheet if need be for web app customers instead of doing them by hand.
A template will need to be made.
Company**
company_id
uuid
NN
company_name
varchar(41)
NN
Company table, this is where users of a company dwell and thier roles. More thought is needed on this, but I am throwing this out there to spec out. Not sure if I need this table or not. Might need to combine it with "Web_App_Customer". Still need to understand the Salesman table. Issue I have is that there are "Salesmen, Managers, Admins, and Accountants." Salesman is easy, but what about the rest?? Do I combine 2 or 3 tables into 1??
Catch_method
catch_method_id
integer
NN
catch_method_name
varchar
NN
Advanced_settings**
advanced_settings_id
uuid
NN
web_app_customer
uuid
NN
will be a work in progress...
Global_settings**
id
integer
NN
I think I need a global settings table to house customer settings. Advanced settings is for advanced settings that don't belong in standard settings like theme, langauage, time zone, ltr, etc.
Global is the DEFAULT settings.
User_settings**
id
integer
NN
I do want settings for a per user basis. For example, some like a specific theme whereas some like darkmode. I want to be able to offer that to them. Not sure if languages would be for global for the customer or on a per user basis. The issue is how would it be a specific language? Like, global is English, but the user is Spanish. Would an admin or manager be able to add another language description to the product?
More things to consider and think out!!
Bill_by
bill_by_id
integer
NN
bill_by_name
varchar
NN
Purchase_orders
purchase_order_id
uuid
NN
web_app_customer
uuid
NN
vendor_id
uuid
NN
pack_date
date
NN
expiration_date
date
tlc_code
varchar(30)
NN
product_quantity
integer
NN
Vendors
vendor_id
uuid
NN
web_app_csutomer
uuid
NN