Metal: Table Design: Difference between revisions

From Bitpost wiki
No edit summary
No edit summary
 
(One intermediate revision by the same user not shown)
Line 13: Line 13:


Each one-way trip can be modeled with 5 inventory records:
Each one-way trip can be modeled with 5 inventory records:
{|
{| class="wikitable"
|Load at Mexican site|trip_detail = On Site, price_id = $/lb, tax_id = Mexican tax, time_stamp = time of load
!trip_detail
!Inventory meaning
|-
|-
|Site to Border|trip_detail = Site To Border, time_stamp = time arriving at border
|On Site
|country_status = 1 (Mexico), price_id = $/lb (Mexican market), tax_id = Mexican tax, time_stamp = time of load
|-
|-
|At Border|trip_detail = At Border, tax_id = Import tax (fee? can it be modeled as percentage?), time_stamp = time leaving border
|Site to Border
|country_status = 1 (Mexico), time_stamp = time arriving at border
|-
|-
|Border to Factory|trip_detail = Border To Factory, time_stamp = time arriving at factory
|At Border
|tax_id = Import tax (fee? can it be modeled as percentage?), time_stamp = time leaving border
|-
|-
|At Factory|trip_detail =  
|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
|}
|}



Latest revision as of 22:19, 29 September 2014


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