[bufalonas]

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

[John Sheppard]

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.

[John Sheppard]

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.

[bufalonas]

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.

[bufalonas]

[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?

[John Sheppard]

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.