Normalizing my Database

Please i have created some tables Delivary with this columns (DelivaryId,DelivaryNo,QtyRecieved,DelivaryDate,ProductId) and Product with this columns (ProductId,ProductCode,ProductName,ProductPrice) as you can see the product table keeps record of products whlie the delivary table keeps record of stock supplied. I will like to create another table that will keep record of stock sold out (Invoice Table) based on the qty recieved from the delivaries table

Please help

I am thinking you need two more tables one for the inventory and another for the dates, delivery date, recieved date, purchase date and more. You want to separate out functional dependencies. Try the links below for sample data models and normalization tutorial. Hope this helps.

http://www.databaseanswers.org/data_models/

http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html

|||

Caddre is right, you probably need at the least, an inventory table (Tracks current stock), a receiving table (Tracks products received, date/time, etc), and a sales table (Tracks products sold, date/time) in addition to your products table.

Create triggers on the receiving table to update the quantities when records are added/updated/deleted.

Same for the sales table.

|||

Motley:

Caddre is right, you probably need at the least, an inventory table (Tracks current stock), a receiving table (Tracks products received, date/time, etc), and a sales table (Tracks products sold, date/time) in addition to your products table.

Create triggers on the receiving table to update the quantities when records are added/updated/deleted.

Same for the sales table.

hi thanks for ur response. The recieving table is my Delivaries Table (DelivaryId,DelivaryNo,Quantity,Date) if i understand u very well u mean i sholud create another table for keeping current stock record but if i do what then happens to the quantity column in the delivaries table

|||It would be the quantity delivered. You would then use that in the insert/update trigger to update the inventory table's quantity (Or what is commonly called "on hand" quantity).|||i think im begining to understand u but please try to expanciate more on what trigger does to a table or table column|||

CREATE TRIGGER (Transact-SQL)

Designing DDL Triggers

You can find more if you search on internet.