设计一个基本的购物车数据库

create table [User]
(
    UserId int primary key identity(1,1),
    FirstName nvarchar(256) not null,
    LastName nvarchar(256) not null,
)

create table Product
(
    ProductId int primary key identity(1,1),
    UnitPrice decimal(18,2) not null, //For catalog purposes.
    Name nvarchar(1000) not null,
    Description nvarchar(max) not null,
    Stock int not null
)

create table [Order]
(
    OrderId int primary key identity(1,1),
    UserId int foreign key references [User](UserId),
    ProductId int foreign key references Product(ProductId),
    UnitCost decimal(18,2) not null, //How much it actually cost when the person bought it.
    ItemCount int not null,
    Subtotal decimal(18,2) not null
)

create table OrderDetail
(
    OrderDetailId int primary key identity(1,1),
    ?

I'm stuck on the database design of the order system.

我坚持订购系统的数据库设计。

A user can choose n products to add to a order request. Any suggestions?

用户可以选择n个产品添加到订单请求中。有什么建议么?

Following some advice given here, how would this feel? Any pitfalls?

根据这里给出的一些建议,这感觉如何?任何陷阱?

create table [User]
(
    UserId int primary key identity(1,1),
    FirstName nvarchar(256) not null,
    LastName nvarchar(256) not null,
)

create table Product
(
    ProductId int primary key identity(1,1),
    UnitPrice decimal(18,2) not null,
    Name nvarchar(1000) not null,
    Description nvarchar(max) not null,
    Stock int not null
)

create table [Order]
(
    OrderId int primary key identity(1,1),
    UserId int foreign key references [User](UserId),
    DateOfOrder datetime not null
)

create table OrderDetail
(
    OrderDetailId int primary key identity(1,1),
    OrderId int foreign key references [Order](OrderId),    
    ProductId int foreign key references Product(ProductId),
    UnitCost decimal(18,2) not null,
    ItemCount int not null,
    Subtotal decimal(18,2) not null
)

1 个解决方案

#1

Typically, you'd have the Order table with the top-level order information (who, when etc) and then an OrderItem (or OrderDetail) table which has a row for each product that forms part of the order including columns like:

通常,您有Order表,其中包含*订单信息(who,when等),然后是OrderItem(或OrderDetail)表,其中每个产品都有一行,构成订单的一部分,包括以下列:

OrderId
ProductId
Quantity
etc

Good candidate for a PK on this OrderItem/OrderDetail table would be on OrderId + ProductId.

此OrderItem / OrderDetail表上PK的良好候选者将在OrderId + ProductId上。

So where you have columns like ProductId, UnitCost, ItemCount etc in the Order table, those are in the wrong place and should be in the OrderItem/OrderDetail table.

因此,如果您在Order表中有ProductId,UnitCost,ItemCount等列,那些位于错误的位置,应该在OrderItem / OrderDetail表中。

Update: To set up a compound PK, you can do:

更新:要设置复合PK,您可以执行以下操作:

create table OrderDetail
(
    OrderId int foreign key references [Order](OrderId),    
    ProductId int foreign key references Product(ProductId),
    ...other columns...,
    CONSTRAINT PK_OrderDetail PRIMARY KEY(OrderId, ProductId)
)

#1