gistfile1.txt

create table stock_adjustments (

  -- for uniqueness
  id int(10) auto_increment,

  -- for chronological ordering, now with microseconds
  date timestamp(6),

  -- these will always refer to a product
  products_id int(10) default null,

  -- possible context
  purchases_id       int(10) default null,
  assemblies_runs_id int(10) default null,
  batch_id           int(10) default null,
  orders_id          int(10) default null,

  -- customers can't make physical stock adjusts...right?
  admin_users_id int(10) default null,

  -- new physical quantity for this product
  new_value int(10) default 0,

  -- how many did it change by
  change int(10) default 0,

  -- oh these old things?
  unit_flat_cost decimal(15,5) default 0.00000,
  unit_full_cost decimal(15,5) default 0.00000,
  new_average_full_cost decimal(15,5) default 0.00000,
  new_average_flat_cost decimal(15,5) default 0.00000,

  primary key (id)
)

create table ordered_quantity_adjustments (
  id int(10) auto_increment,
  date timestamp(6),

  -- these could be an orders_products_id
  -- but i think this is better
  -- opinion?
  products_id int(10) default null,
  orders_id int(10) default null,

  -- one of these can be made by either, right?
  admin_users_id int(10) default null,
  customers_id int(10) default null,

  -- total number of this product on new orders at this time
  new_value int(10) default 0,

  -- how many it is changing by
  change int(10) default 0,

  primary key (id)
)