Please consider changing
create table products(product nvarchar(max),
price numeric(18,2),
discount numeric(18,2) default 0)
into something like
create table product_categories
(
id bigint IDENTITY not null primary key,
name nvarchar(255) not null,
CONSTRAINT UNQ_PRODUCT_CATEGORY_NAME UNIQUE(NAME)
)
go
create table products
(
id bigint IDENTITY not null primary key,
name nvarchar(255) not null,
Category bigint not null,
description nvarchar(max),
CONSTRAINT FK_PRODUCT_CATEGORY FOREIGN KEY(Category) REFERENCES product_categories(id),
CONSTRAINT UNQ_PRODUCT_NAME UNIQUE(Category,name)
)
go
create table product_price
(
id bigint IDENTITY not null primary key,
product bigint not null,
fromTime dateTime2 not null default SYSDATETIME(),
price numeric(18,2),
discount numeric(18,2) default 0,
CONSTRAINT FK_product_price_PRODUCT FOREIGN KEY(product) REFERENCES products(id),
CONSTRAINT UNQ_PRODUCT_PRICE UNIQUE(product,fromTime)
)
go
What you've got isn't very useful, and consider decomposing it even further, as discount doesn't really belong together with the base price for the product at a given time ...
Regards
Espen Harlinn