主页 / dba / 问题 / 234134
Accepted
Sook Lim
Asked: 2019-04-08 08:52:15 +0800 CST

我如何绘制 erd 来模拟这种情况？

  • 772

客户需要支付活动本身的费用以及作为活动一部分使用的任何“额外”项目的费用。

所以我有客户实体（由客户 ID 标识）和一个项目实体（由项目 ID 标识）。这些中的每一个都有一个成本属性来确定每个项目/活动的成本。但是我如何为这个客户的购买建模呢？例如，客户可能为一项活动付费并为该活动购买一些物品。

我可以添加一个名为 billingInfo 的新实体吗？或者根本不应该有一个新实体？

database-design erd

1 个回答

  1. Best Answer

    将“账单”本身视为具有关联实体（活动和项目）的实体。像这样的东西：

    实体模型

    客户、项目和活动都应该是单独的实体，并且您应该为销售（账单）和销售行项目（账单的活动和项目的一部分）包括一个单独的实体。这样，您可以一次定义项目和活动，并在单个销售实体记录中包含多个（或没有）项目和活动。下面的例子。

    设置：

    USE master
GO
CREATE DATABASE Sales
GO
USE Sales
GO

CREATE TABLE Customers (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    CustomerName NVARCHAR(255)
)
GO

INSERT INTO Customers (CustomerName)
VALUES ('Joes Appliances'), ('Franks Bar & Grill')
GO

CREATE TABLE Items (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    ItemName NVARCHAR(255),
    ItemPrice DECIMAL(10,2)
)
GO

INSERT INTO Items (ItemName, ItemPrice)
VALUES ('Refrigerator', 599.99),
    ('Oven', 1299.99),
    ('Television', 249.99)
GO

CREATE TABLE Activities (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    ActivityName NVARCHAR(255),
    ActivityPrice DECIMAL(10,2)
)
GO

INSERT INTO Activities (ActivityName, ActivityPrice)
VALUES ('Install (per hour)', 29.99),
    ('Install (flat fee)', 80.00)
GO

CREATE TABLE SalesHeader (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    CustomerID INT NOT NULL,
    SaleDate DATETIME,
    CONSTRAINT FK_SalesHeader_Customer FOREIGN KEY (CustomerID)
    REFERENCES Customers (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
GO

CREATE TABLE SalesActivities (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    SalesHeaderID INT NOT NULL,
    ActivityID INT NOT NULL,
    Quantity INT NOT NULL,
    CONSTRAINT FK_SalesActivities_SalesHeader FOREIGN KEY (SalesHeaderID)
    REFERENCES SalesHeader (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT FK_SalesActivities_Activity FOREIGN KEY (ActivityID)
    REFERENCES Activities (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
GO

CREATE TABLE SalesItems (ID INT IDENTITY PRIMARY KEY CLUSTERED,
    SalesHeaderID INT NOT NULL,
    ItemID INT NOT NULL,
    Quantity INT NOT NULL,
    CONSTRAINT FK_SalesItems_SalesHeader FOREIGN KEY (SalesHeaderID)
    REFERENCES SalesHeader (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT FK_SalesItems_Item FOREIGN KEY (ItemID)
    REFERENCES Items (ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
GO

    测试数据：

    -- Sales example #1: Joe's Appliances purchased 1 refrigerator, 1 oven and 5 televisions, plus an install with a flat fee

INSERT INTO SalesHeader (CustomerID, SaleDate)
VALUES (1, '2018-09-02')

INSERT INTO SalesActivities (SalesHeaderID, ActivityID, Quantity)
VALUES (1, 2, 1)

INSERT INTO SalesItems (SalesHeaderID, ItemID, Quantity)
VALUES (1, 1, 1),
    (1, 2, 1),
    (1, 3, 5)


-- Sales example #1: Franks Bar & Grill purchased 3 ovens, 2 refrigerators and 12 televisions, plus an install with an hourly rate for 5 hours

INSERT INTO SalesHeader (CustomerID, SaleDate)
VALUES (2, '2017-02-24')

INSERT INTO SalesActivities (SalesHeaderID, ActivityID, Quantity)
VALUES (2, 1, 5)

INSERT INTO SalesItems (SalesHeaderID, ItemID, Quantity)
VALUES (2, 2, 3),
    (2, 1, 2),
    (2, 3, 12)

    询问：

    SELECT c.CustomerName,
    sh.ID AS [InvoiceID],
    sh.SaleDate,
    a.ActivityName AS [InvoiceLineType],
    sa.Quantity AS [InvoiceLineQuantity],
    a.ActivityPrice AS [InvoiceLinePrice]
FROM SalesHeader sh
INNER JOIN Customers c ON sh.CustomerID = c.ID
LEFT JOIN SalesActivities sa 
    INNER JOIN Activities a ON a.ID = sa.ActivityID
ON sa.SalesHeaderID = sh.ID
UNION
SELECT c.CustomerName,
    sh.ID AS [InvoiceID],
    sh.SaleDate,
    i.ItemName AS [InvoiceLineType],
    si.Quantity AS [InvoiceLineQuantity],
    i.ItemPrice AS [InvoiceLinePrice]
FROM SalesHeader sh
INNER JOIN Customers c ON sh.CustomerID = c.ID
LEFT JOIN SalesItems si 
    INNER JOIN Items i ON i.ID = si.ItemID
ON si.SalesHeaderID = sh.ID

    结果：

    CustomerName        InvoiceID   SaleDate                InvoiceLineType     InvoiceLineQuantity InvoiceLinePrice
----------------------------------------------------------------------------------------------------------------
Franks Bar & Grill  2           2017-02-24 00:00:00.000 Install (per hour)  5                   29.99
Franks Bar & Grill  2           2017-02-24 00:00:00.000 Oven                3                   1299.99
Franks Bar & Grill  2           2017-02-24 00:00:00.000 Refrigerator        2                   599.99
Franks Bar & Grill  2           2017-02-24 00:00:00.000 Television          12                  249.99
Joes Appliances     1           2018-09-02 00:00:00.000 Install (flat fee)  1                   80.00
Joes Appliances     1           2018-09-02 00:00:00.000 Oven                1                   1299.99
Joes Appliances     1           2018-09-02 00:00:00.000 Refrigerator        1                   599.99
Joes Appliances     1           2018-09-02 00:00:00.000 Television          5                   249.99
    • 0

相关问题