title: What are the pros and cons of the following database design?
body: <p>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).  </p>
  
  <pre><code>-- 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)
  </code></pre>