Storing updates from different updaters in the History table

Question

I have a table "AvailableProducts" with following fields:

StoreID int,
ProductID int,
ProductPrice decimal,
IsAvailable bit

The ProductPrice can be changed either by sales person in the store or it can be updated by a price update from the brand. Now to store the history of price changes, I've created a history table as follows:

Table ProductPriceHistory
UpdateID int,
StoreID int,
ProductID int,
ProductPrice decimal,
IsAvailable bit,
UpdatedBy int,
UpdatedAt datetime

The problem I am facing is that keeping BrandID or SalesPersonID (That made the changes to price) in the UpdatedBy field is wrong design.

I can modify it to something like this:

Table ProductPriceHistory
UpdateID int,
StoreID int,
ProductID int,
ProductPrice decimal,
IsAvailable bit,
BrandId int,
SalesPersonID int,
UpdatedAt datetime

This would allow me to reference the updating entity by a foreign key in the Brand and SalesPerson Tables using the Id fields. But it would also lead to many empty or null column values since only one entity i.e. either brand or SalesPerson can update the price at given time.

I could also create two different history tables to save updates made by SalesPerson and Brands separately but this solution doesn't look appealing. Any suggestions for improvement in this design as I would like the history for this table to be maintained in a single table. Thanks :)

score 0 · Answer 1 · answered Apr 10 '17 at 10:34

You could create an ObjectType table with 2 items:

CREATE TABLE [dbo].[ObjectType](
    [ObjectTypeId] [int] NOT NULL,
    [ObjectTypeName] [nvarchar](100) NULL)

GO
INSERT INTO dbo.ObjectType VALUES (1, 'Brand')
INSERT INTO dbo.ObjectType VALUES (2, 'SalesPerson')

Then add a new column ObjectTypeId to table ProductPriceHistory

ALTER TABLE ProductPriceHistory
ADD ObjectTypeId int

You could write log for many kinds of item not only for SalesPerson and Brands

score 0 · Answer 2 · edited May 23 '17 at 11:46

This is a common question - it's often asked in relation to the object orientation concept of polymorphism.

There are 3 standard solutions - you've identified two of them; the final one is to model the common fields in a single table, and have separate tables for the variant data. That would have tables "sales_update" and "brand_update", with foreign keys on updateID back to the update history table.

There is no elegant solution - the relational model simply doesn't support this use case particularly nicely. You need to look at the rest of your system, and pick the solution that's easiest in your case. Usually, that's the "one table stores everything model" - but your situation may be different.

Storing updates from different updaters in the History table

2 Answers2