轮胎采购系统SQL数据库的设计

Question

好的，我没有这方面的代码，因为我不知道它会如何进行。以这个场景为例，一个购买系统，比方说，轮胎。

我有一个名为tStock的表，另一个是为客户命名为cOrder的表。

tStock有以下列：

(PK) Tyre_ID
(VARCHAR) Manufacturer
(int) Quantity

而cOrder有：

(PK) Customer_ID
(varchar) Cust_name
(int) Purchase_quantity

我希望实现的是，当我有一个表格，获取客户的详细信息和订单有多少轮胎(S)，他想要最小的轮胎最多4个轮胎，然后它将从数据库中减去这个金额并更新它。

我是否正确地认为，我需要让customer_ID和purchase_quantity作为外键约束才能更新？

它还需要以某种方式连接起来吗?比如外部的，等距的，自然的？

如有任何建议，将不胜感激。

Answer 1

对于一个采购系统，我至少有三张桌子。一个为客户，一个为产品，一个为订单。

    --Customer
CREATE TABLE [dbo].[Customer](
    [CustomerId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [AdditionalInfoRequired] [varchar](50) NOT NULL,
 CONSTRAINT [CustomerId] PRIMARY KEY CLUSTERED 
(
    [CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
--Product
CREATE TABLE [dbo].[Product](
    [ProductId] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [varchar](50) NOT NULL,
    [Quantity] [int] NOT NULL,
 CONSTRAINT [ProductId] PRIMARY KEY CLUSTERED 
(
    [ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
--Order
CREATE TABLE [dbo].[Order](
    [OrderId] [int] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NOT NULL
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
(
    [OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order]  WITH CHECK ADD FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([CustomerId])
GO
--OrderDetails
CREATE TABLE [dbo].[OrderDetails](
    [OrderDetailsId] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [Quantity] [int] NOT NULL,
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED 
(
    [OrderDetailsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderDetails]  WITH CHECK ADD  CONSTRAINT [FK_OrderDetails_Product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([ProductId])
GO
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_Product]
GO

如果你有什么问题请告诉我。