Results 1 to 6 of 6
  1. #1
    Join Date
    May 2008
    Posts
    9

    Lightbulb Inventory Database tables

    Ok here we go I have come up with the following tables but as i will show you i will need more tables to be integrated in the system. First, an overview of what i wan the system to accomplice is

    1. It should capture data from the suppliers of the medical facilities and drugs.
    2. It should be able to show the records of what is in stock for all the
    diferent Items.
    3. It should be able to record the different placed orders by the hospital to
    the different suppliers.
    4. The system should be able truck the in-flow and out-flow of items in stock.
    5. It should be able to print reports at each level
    Tables
    SALE
    {Reciept_Num}, sale_date
    INVOICE
    {VendorID},Invoice_Num, Invoice_Date,Paid_or_Not
    ITEM SALE
    {Receipt_Num, Sale_Item_Num}, Quantity_Sold, Product_ID
    INVOICE_ITEM
    {Invoice_Num, Invoice_Item}, Qty_Added, Item_Num
    STOCK
    {Item_num}, Item_Description, Qty_in_Stock, Type_of_Item, Minimum_OrderQty
    PRODUCTS
    {Product_ID}, Product_Description

    what should be noted is that the sale of medic facilities has not catered for the addmited patients who should be charged for the drugs used on discharge. This means that the system should inclute a table for PATIENTS and probably prices. How i should do that is not clear to me yet

  2. #2
    Join Date
    May 2008
    Posts
    12
    If you intend your system to grow you are going to run into many problems with this schema.

    STOCK
    It is bad practice to store the qty_in_stock. Obviously im infering, but I suspect thats what you want to do. Doing this will cause you all kinds of headaches. Not so much with your queries but with your front end. You will have to write code to update the qty_in_stock in many many events.

    Additionaly and this is probably equally important. You cannot audit your stock trail if you do it the above way.

    It is better to have your qty_in_stock calculated on the fly. That is what you have in stock is the sum of the quantity purchased minus the quantity sold. To that sum you also have to take into account shrinkage (stock that goes mysteriously missing) and a few other things.

    To do this. You would have the following tables
    STOCK
    {Item_num}, Item_Description, Type_of_Item, Minimum_OrderQty
    STOCKTAKE
    {stocktake_id}, item_num, qty_in_stock, date
    SALE
    {sale_id}, date, address, etc
    SALELINE
    {sale_line_id}, sale_Id,item_num, qty
    PURCHASE
    {purchase_id}, date, address, etc
    PURCHASELINE
    {purchase_line_id}, purchase_Id,item_num, qty

    Hope that helps.

  3. #3
    Join Date
    May 2008
    Posts
    12
    If you intend your system to grow you are going to run into many problems with this schema.

    STOCK
    It is bad practice to store the qty_in_stock. Obviously im infering, but I suspect thats what you want to do. Doing this will cause you all kinds of headaches. Not so much with your queries but with your front end. You will have to write code to update the qty_in_stock in many many events.

    Additionaly and this is probably equally important. You cannot audit your stock trail if you do it the above way.

    It is better to have your qty_in_stock calculated on the fly. That is what you have in stock is the sum of the quantity purchased minus the quantity sold. To that sum you also have to take into account shrinkage (stock that goes mysteriously missing) and a few other things.

    To do this. You would have the following tables
    STOCK
    {Item_num}, Item_Description, Type_of_Item, Minimum_OrderQty
    STOCKTAKE
    {stocktake_id}, item_num, qty_in_stock, date
    STOCKADJUSTMENT
    {stockadjustment_id}, item_num, adjustment_qty, date
    SALE
    {sale_id}, date, address, etc
    SALELINE
    {sale_line_id}, sale_Id,item_num, qty
    PURCHASE
    {purchase_id}, date, address, etc
    PURCHASELINE
    {purchase_line_id}, purchase_Id,item_num, qty

    Hope that helps.

  4. #4
    Join Date
    May 2008
    Posts
    9
    Thanks John but what is with the STOCKADJUSTMENTS, SALELINE and PURCHASELINE TABLES? Whats there significance and besides that i need to know how best i can include the CUSTOMERS/PATIENTS TABLES so that i can be able to process a report for a particular patient when necessary of how much they have consumed.

  5. #5
    Join Date
    May 2008
    Posts
    9

    Inventory tables

    [QUOTE=STOCK
    {Item_num}, Item_Description, Type_of_Item, Minimum_OrderQty
    STOCKTAKE
    {stocktake_id}, item_num, qty_in_stock, date
    STOCKADJUSTMENT
    {stockadjustment_id}, item_num, adjustment_qty, date

    Hope that helps.[/QUOTE]

    Of course it does help I also realise how my first tables will cause me problems with my front end however in the above quote of your previous posting, I have failled to know the exact difference in the above 3 tables and theire roles. may you please explain why the tree tables are of need?

  6. #6
    Join Date
    May 2008
    Posts
    12
    Sorry for the delay in response.

    Perhaps have a read of this. This will help you understand better than I could explain.

    http://allenbrowne.com/AppInventory.html

    Look through the whole website. That guy is a guru.

Posting Permissions

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