[gnue-sb-discuss] Product Management schema

[Mike Vincent]

Ok, I've been doing some more thinking, specific to our current focus of
product/item management. I've started designer several times with the
intention of rewriting item_main.gfd from scratch, but the underlying
schema needs some reworking before that.. Here are my thoughts on it..  

item table
  id
  sku
  oldsku (refsku?)
  upc
  status
  size (physical, maybe derived from a preset list of 'size classes')
  weight
  stdprice
  price1
  price2
  price3
  price4
  ... ? Is there a better way to do this? 
        Is there a genericly acceptable number of price variations?
  I think 5 or less seem to be most common in my industry. Should
  we maybe move it to another table, such as has been done for so many
  other things? In that way, you could name it what you wanted, as well
  Employee Price, Wholesale price, 1 star customer, 2 star, ..whatever.

  Some things maybe, maybe not..?
  department 
  glacct (might be easier to wait and do ar/ap/gl stuff at one time)
  notes?
 
sources table (so we can source an item through multiple vendors)
  id
  sku
  vendor
  pref (a score showing preference to certain vendors vs others)
  lastcost (might be handy for weighting priority based on price)
  lastdate (keep the weighting sane, wouldnt want some 5yr old price
            mucking up the works, right?)

inventory table
  id
  sku
  serialnumber
  lot
  location
  unitofmeasure
  qtyonhand
  date_created
  date_recvd
  date_shipped
  date_adjusted

pricing.. pricing pricing pricing.. 

  stdprice
  price1
  price2
  price3
  price4
  ... ? Is there a better way to do this? 
        Is there a genericly acceptable number of price variations?
  I think 5 or less seem to be most common in my industry.
 
  I am thinking that when I setup my clients, I will want a field to
  specify which column of pricing will be used for the client as their
  standard pricing, for most it would be 'the' std price but I may want
  to set it to one of the others for a high volume client. 

  For me, which column to use is based on the volume of a given product
  that is being purchased. Presently, I base the volume on the sum per
  catalog#, which is the item without variations (color, size) taken 
  into account. So as long as someone orders a total equal or greater
  to a certain qty of Nike model#167316, I dont care what colors or 
  sizes they order, I'll give them a better than std price. The actual
  'watermark' for where those price breaks happen differs depending on 
  the product. Typically it's something like stdpricing upto 23pcs, 
  then Tier1 pricing until 47pcs, then Tier2 pricing until 143pcs,
  then Tier3 until.. etc. Maybe the best way to acheive that is to have
  yet another table to hold those volume watermarks per product? 

  I have another wish list item in all this.. 
  It would be really nice if the system offered some level of protection
  from selling things below a certain level of profit. I mean, it would
  be nice if there were another form where I could key in values for 
  markup and discounts (for the tiers) and have it use these values to
  in combination with the values of cost of goods from purchases to make
  sure that the prices are in line, probably automagically correcting 
  prices where needed or at least raising a flag.

  Ideas? Comments? Hammers?
  
  I hope I'm not overcomplicating things, I do have a knack for that. 

-- 
Mike Vincent
Tops Embroidery Works
Keller, Texas
http://www.topsew.com