dcsimg
A Flash Developer Resource Site

Results 1 to 3 of 3

Thread: stumbling block on my Database design, stock tracking.. help.

  1. #1
    Special Member Tea_J's Avatar
    Join Date
    Dec 2000
    Posts
    991

    stumbling block on my Database design, stock tracking.. help.

    hi guys

    i basically just need your ideas ..

    I am developing an inventory system inventory system for products catering to both SERIALIZED and CONSUMABLES

    Primary Tables are:

    PRODUCTS
    Master table for product details like name, brand, model, specifications.. also has important detail such as
    - serialized = 0/1 (if product has serial/trackable)
    - stocks_new = all available stocks for sale
    - stocks_all = all availabel stocks for sale, new or old/used/refurbished
    (stocks_new and stocks_all columns are automatically updated at every transaction)

    STOCKS
    This is a details table for each individual serialized stocks, maintaining actual price / stock, serials, and other unique info to each stock.. this is generally for history/tracking.. includes colums:
    - pid (prodcut id, related to Products table)
    - serial
    - cuid (customer id)
    - soid (sales order ID)
    - oid (order item ID, somewhat related to sales order above)
    - drno_in (Delivery Receipt from Vendors)
    - drno_out (Delivery Receipt given to our CUSTOMERS)



    SALES_ORDERS
    Master table for all sales orders.. has columns:
    - soid
    - cuid


    ORDER_ITEMS
    Details table for SALES ORDERS, contais each order line... has columns:
    - oid
    - soid (sales order id , above)
    - pid (product id)
    - sid (stock ID for stock specific items)
    - qty (how many quantities)
    - price (price per unit)
    - drid (Delivery receipt ID)

    DELIVERY_RECEIPTS
    Table to hold the actual delivery records to customers, where Sales Order is the request to fullfill , and Delivery Receipt is the record of actual delivery to customers.. includes the followin columns:
    - drid (delivery receipt id)
    - soid (sales order ID)
    - stamp (unix time for when delivery was made)




    Ok, as an overview, sales staff creates the SALES ORDER, each sales order entry will have order items specific to each sales order.. data would look like:

    for SALES ORDER # 001, order item would include:

    oid/pid/stid/price/qty
    ORDER ITEM 001 / 12 / 32 / 2.50 / 1 (since this has a specific stock (stid), means this order item has a specific stock with serial etc, therefore quantity is always set to 1)

    ORDER ITEM 002 / 13 / 0 / 3.99 / 10 (this is consumable type of product, so this order item may have 1 or more qty)



    Tracking wise, all is well up to this point.. each stock will contain the drno_out indiciate w/ delivery receipt batch it was part of, and a sales order id from w/c this item was sold with...

    and when i need to know the serial numbers of the items related to that sales order/delivery receipt, i can simply look up stocksk w/ the same drid in the drno_out colum...


    Now, my dilema is , in REAL life, there are situations where stocks are recalled, returned for replacement, or even exchanged for other items.. this can also mean a specific stock can be received and resold more than 2 times.. ..

    HOW DOES ONE PROPERLY TRACK THIS?


    Ofcourse I would think , why not a transactions table, or perhaps generating 1 Delivery REceipt entry / stock / per activity (receiving , and selling/sending out)..

    this would easily solve my problem.. but then i got to thinking, what if there was an order for 100pcs LCD monitors... and these monitors where returned and exchanged for another type of product.. and then these 100pcs were SOLD again to another customer.. and then returned for RMA.. (just a sample scenario but still possible)..

    basically the above scenario involves these 100 monitors being in and out of the shop for a lot of times already.. and GENERATING 1 DR entry (or even an entry in a new DR Items table) per stock, means a whole lot of entries (and clutter).. even if i used transactions table to monitor each movement of each stock, w/c is basically like the DR table .... it's the same clutter issue.. and this is just one product.. there are even instances of 100 computer units being sold, each computer unit containing 10 or more items (lcd, procesor, motherboard, etc) ... that's an easy 1000 entries in the DR or TRANSACTIONS /LOGS table.. per movement..

    and dont get me started w/ WAREHOUSE TRANSFERS? what if you would transfer an item from 1 warehouse to another a few times.. this means thousands of entries/rows easily..


    IS THERE A BETTER WAY of achieving tracking and stuff? What do you guys think about my situation?? Am i just being soo paranoid of the thousands of little movement entries, w/c mysql is actually built for and i shouldnt worry about??


    i'm pretty stuck .. i know several solutions to achieve what i want specially in tracking but i guess my issue is OPTIMIZATION of design... i just wana make sure im right ...


    Really hope for some feedbacks..

    TJ

  2. #2
    Special Member Tea_J's Avatar
    Join Date
    Dec 2000
    Posts
    991
    im thinkin of just adding a field in the ORDER ITEMS entry and store all stock id's related to that order..


    for example, one order item entry could be

    ID=001, intel processor, x 10pcs

    i would then put all the IDs "actual stocks" of the actual stocks chosen for this order item when scanning the serial numbers, into 1 field,delimeted by a comma

    stock_ids = "2,33,4,5,52,45,".. and so on..

    hmm

  3. #3
    Special Member Tea_J's Avatar
    Join Date
    Dec 2000
    Posts
    991
    .. or "F" it.. so what if i have another entry per transaction per item in the database? so what if it can easily go up to 10,000 records in under 1 month.. right? it provides the best means of tracking each stock item, as well as easier coding. hmmm.. i think im just being tooo paranoid or OPTIMIZATION freak ..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  




Click Here to Expand Forum to Full Width

HTML5 Development Center