Metal: Table Design
Inventory
Inv_id number -- PK Metal_id number -- fk to metal_type Weight number -- material transported weight price_id number -- fk to price_list trans_id number -- fk to transport_container tax_id number -- fk to tax_info trip_id number -- fk to trip_detail user_id_last_mod number -- fk to users time_stamp date -- last edit date
Each one-way trip can be modeled with 5 inventory records:
| trip_detail | Inventory meaning |
|---|---|
| On Site | country_status = 1 (Mexico), price_id = $/lb (Mexican market), tax_id = Mexican tax, time_stamp = time of load |
| Site to Border | country_status = 1 (Mexico), time_stamp = time arriving at border |
| At Border | tax_id = Import tax (fee? can it be modeled as percentage?), time_stamp = time leaving border |
| Border to Factory | country_status = 2 (US), time_stamp = time arriving at factory |
| At Factory | country_status = 2 (US), price_id = $/lb (US market), time_stamp = time of unload |
Metal_type
Metal_id number -- PK Metal string -- Metal_name
Price_list
price_id number -- PK value number --maybe based on penny or something...might string it depending
Transport_container
trans_id number -- PK trans_name string -- trans name,crew
Trip_detail
trip_id number -- PK trip_detail string -- site to border, border to process, process to border, border to site
Tax_info
tax_id number -- PK tax_calc number -- tax rate
Users
user_id number -- PK user_name string -- username Lang_id number -- fk language_translate
User_priv
uniq_id number -- PK priv_level number -- 1 admin, 2 user user_id number -- fk users
Language_translate
Lang_id number -- PK Field_id number -- field in page maybe text_string string -- text to put in field
Inventory_audit
Inv_id number -- PK Metal_id number -- fk to metal_type Weight number -- material transported weight price_id number -- fk to price_list trans_id number -- fk to transport_container tax_id number -- fk to tax_info user_id_last_mod number -- fk to users time_edit date -- when entry was updated