What are the pros and cons of the following database design?

Question

I am looking to for suggestions and commments about the following design. All the tables are expected to be large tables (millions of records) and are updated and queried often and any kind of update is allowed (update, insert, delete).

-- product
create table Product(
productID int not null identity(1,1),
name varchar(100) not null,

constraint PK_Product primary key(productID)
)

-- client
create table Client(
clientID int not null identity(1,1),
name varchar(100) not null,

constraint PK_Client primary key(clientID)
)

-- order
create table [Order](
orderID int not null identity(1,1),
clientID int not null,
orderDateTime datetime not null,
orderAmount money not null,
orderNote varchar(max) null,

constraint PK_Order primary key(orderID),
constraint FK_Order_Client foreign key(clientID) references Client(clientID)
)

exec sp_tableoption 'Order', 'large value types out of row', 0

create index IX_Order_client on [Order](clientID)

-- items
create table OrderItem(
orderItemID int not null identity(1,1),
orderID int not null,
productID int not null,
qty int not null,
amount money not null,

constraint PK_OrderItem primary key(orderItemID),
constraint FK_OrderItem_Order foreign key(orderID) references [Order](orderID),
constraint FK_OrderItem_Product foreign key(productID) references Product(productID)
)

create index IX_OrderItem on OrderItem(orderID)

Answer 1

this looks pretty good.

you may also like to have an adjustment amount for any discounts or additions to the overall fee of the order. It is not very clear how you intend to handle the order amount that you show - normally, the order total could be calculated from the sum of the included parts.

also, if applicable, consider a ship method for indicating how the order will be delivered, and maybe a status if the order takes several steps to accomplish.