Database Design – How to Draw an ERD for a Specific Scenario

database-designerd

Customers are billed for the cost for the activity itself and also any "extra" items which are used as part of an activity.

So I have the customers entity (identified by customer id) and an item entity(identified by item id). Each of these has a cost attribute to determine the cost of each item/activity. But how do I model the purchases of this customer? E.g. the customer may pay for an activity and buy a few items for the activity.

Can I add a new entity called billingInfo?Or should there not be a new entity at all?

Best Answer

Consider the "bill" itself as an entity that has associated entities (activities and items). Something like this:

entity model

Customers, Items and Activities should all be separate entities, and you should include a separate entity for Sales (Bills) and Sales Line Items (Activities and Items part of the Bill). This way you can define Items and Activities once and include multiple (or no) Items and Activities on a single Sale entity record. Example below.

Setup:

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

Test Data:

-- 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)

Query:

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

Results:

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
Related Question