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

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),
co开发者_高级运维nstraint 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)

---

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.

---

1)For OrderItem table, I think is better to store the unit price and to to add a calculated field for amount: Amount AS Qty*UnitPrice [PERSISTED]. Also, the data type for UnitPrice & Amount fields is important. Are you sure that you need 4 decimals ? Would not be better to use only 2 decimals (ex. NUMERIC(8,2)) ?

2) At this moment, using proposed design, is possible to "duplicate" order items (order id & product id) easyly because OrderItem table do not have any restriction:

Order (1001, ...)
OrderItem (1,1001,10,400,800),(2,1001,11,200,1200),(3,1001,10,400,800).

The solution is to add an unique index:

CREATE UNIQUE INDEX IUX_OrderItem_OrderID_ProductID 
ON OrderItem (OrderID, ProductID)

In some cases, OrderID + ProductID can be duplicated but UnitPrice will be diferent. If this is your case, then the unique index will have a key with 3 fields: CREATE UNIQUE INDEX IUX_OrderItem_OrderID_ProductID_UnitPrice ON OrderItem (OrderID, ProductID, UnitPrice)

3) If SQL Server version is >= 2005 then you may use schemas for database objects.

CREATE SCHEMA Sales;
CREATE TABLE Sales.[Order] (...);
CREATE TABLE Sales.OrderItem (...);

4) My advice is to not create indexes (IX_OrderItem) without a reason: for example a query or a restriction. They need to be updated at every DML operation and they need storage space. If you want to create indexes try to create unique indexes if possible.

5) I do not understand the reason to use VARCHAR(MAX) data type for orderNote field from Order table. VARCHAR(8000) or NVARCHAR(4000) is not enough ? You want to insert a novel in that field for every order ?