How to manage item price in database

Question

I have two table in my application, one is "item" and "itemprice" and these are already created by a previous developer. The itemprice table contain fields like, unitOfSale , itemPoint, itemSalePrice and itemPurchasePrice, tax. There are some fields those are duplicate in item and itmPrice table. Can i merge itemPrice table in item table. will it do any negative imapact on architecture, Do I need to keep the history of itemprice when it changes? If I need to keep history of price then which architecture should i follow ? one table (merged table) or two table?

Packiaraj Santhiyagu · Answer

Hi, get idea from data model designer. He is the right person to decide it.

Nitin Sontakke · Answer

It is my strictly personal opinion that such decisions are strategic and are to be taken by information owner only. Someone from the forum will not be able to comment on situation which is unique to you. But, please wait for the answer, if someone can give.

Bhairab Dutt · Answer

Hi, I think you can add the price details in the table with encryption and when you you get the price you can use decryption to show the price with the help of any algo as Rijndael. The price table you can add seperatly with your required details and column. Hope its help you , Please accept the answer if its help to you. Thanks.

Hemant Jindal · Answer

Hi Asif In general rule, we should keep item characterstics like ItemCode, ItemNumber, ItemDescription (properties which will not change over time) in one table and we should keep properties like itemprice, itemtax in a separate table because there is a possiblity that we may revise our itemprice with time. if we keep item and itemprice details in one table we will end up in having duplicate data like ItemCode, ItemNumber, ItemDescription for item whenever we will revise the itemprice. Please note that 1-many relationship exists between Item and ItemPrice table I hope keeping two separate table approach might help us in furture as well Thanks Hemant Jindal