A Normalization issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

A Normalization issue

Author

Topic

June
Starting Member

18 Posts

Posted - 2009-03-13 : 01:59:42

Hi,
I have a table which stores the attributes for a material such as material code, description, unit etc. Now if I include the current balance of the material as an attribute will it be considered as good normalization practice i.e..

Material

Code Description Unit Balance

I also have an IssueDetails table which contains a trigger that updates the Balance in Material table on issue of a material.

IssueDetails

IssueDate MaterialCode IssueQuantity Rate

the trigger is as follows...

CREATE TRIGGER updateMaterial ON dbo.IssueDetails
FOR INSERT
AS

UPDATE m SET m.Balance=(m.Balance- i.IssueQuantity)
FROM Material m JOIN inserted i ON m.Code = i.MaterialCode

any suggestion in improving this design will be very much welcomed.

souLTower
Starting Member

39 Posts

Posted - 2009-03-13 : 06:25:30

That depends. The short answer is no. By using the current balance of the item as an attribute you lose the history of the transactions on the item. If you are never going to store the transactions then saving the balance would be acceptable. The correct normalization would be to not include the balance at all but to always get it by calculating the transactions.

The bottom line here is that you can get the data from somewhere else by using a query. Having the same data stored in 2 places is a big no no. How do you know which one you can trust? What if your trigger has an issue and the field is not updated? What if some bad code updates the quantity field?

I hope this helps

God Bless

June
Starting Member

18 Posts

Posted - 2009-03-13 : 07:04:45

quote:
Originally posted by souLTower

If you are never going to store the transactions then saving the balance would be acceptable.

Thanks souLTower. I dont need to store the transactions and thats why I had chosen this design. Yes, I can store the balance in the IssueDetails table, but then I will need to update it when I receive some material. So instead of updating it in IssueDetails, I decided to do it in Material itself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources