-1

I want to design database to store products and we have following products:

  • Single products:
    • A
    • B
    • C
  • Group products
    • D (made from A and C)
    • E (made from A and B)

Stores can sell single product like A,B as well as Group products like

  • D {A,C}
  • E { A,B}

I am looking for help understanding how best to store the products (all products in one table, or a table for single products, and a separate table for group products), and how I should include products in an order.

0

3 Answers 3

2

I will suggest you to create 3 tables like this

CREATE TABLE SingleProducts
(
    ProductID INT IDENTITY PRIMARY KEY,
    ProductName VARCHAR(50)
);
GO
CREATE TABLE GroupProducts
(
    GroupProductID INT IDENTITY PRIMARY KEY,
    GroupProductName VARCHAR(50)
);
GO
CREATE TABLE GroupProductsParts
(
    GroupProductID INT
        CONSTRAINT FK_GroupProductsParts_GroupProducts
        FOREIGN KEY REFERENCES dbo.GroupProducts (GroupProductID),
    ProductID INT
        CONSTRAINT FK_GroupProductsParts_SingleProducts
        FOREIGN KEY REFERENCES dbo.SingleProducts (ProductID),
    Position INT --this column is required if you want to know the ----
-- SingleProducts position in the GroupProducts
);
GO

Your single products will be kept in the 1st table, GroupProducts in the 2nd, and the relation between them in the 3rd. For example

INSERT INTO dbo.SingleProducts
(
    ProductName
)
VALUES ('A' -- GroupProductName - varchar(50)
       ),
('B'),
('C');

INSERT INTO dbo.GroupProducts
(
    GroupProductName
)
VALUES ('D' -- GroupProductName - varchar(50)
       ),
('E');

INSERT INTO dbo.GroupProductsParts
(
    GroupProductID,
    ProductID,
    Position
)
VALUES
(   1, -- GroupProductID - int
    1, -- ProductID - int
    1  -- Position - int
),
(1, 3, 2),
(2, 1, 1),
(2, 2, 2);

Now if you want to know which SingleProducts does my GroupProduct have, you can write a simple query with 2 joins.

SELECT sp.ProductName,
       GroupProductName,
       gpp.Position
FROM dbo.SingleProducts sp
    JOIN dbo.GroupProductsParts gpp
        ON gpp.ProductID = sp.ProductID
    JOIN dbo.GroupProducts
        ON GroupProducts.GroupProductID = gpp.GroupProductID;

ProductName                                        GroupProductName                                   Position
-------------------------------------------------- -------------------------------------------------- -----------
A                                                  D                                                  1
C                                                  D                                                  2
A                                                  E                                                  1
B                                                  E                                                  2

In order to keep information about product sales you will need another table like this...

CREATE TABLE ProductSales(
SaleId INT IDENTITY, 
GroupProductID INT
        CONSTRAINT FK_ProductSales_GroupProducts
        FOREIGN KEY REFERENCES dbo.GroupProducts (GroupProductID),
    ProductID INT
        CONSTRAINT FK_ProductSales_SingleProducts
        FOREIGN KEY REFERENCES dbo.SingleProducts (ProductID), 
        SaleDate DATE, 
        [another column specific to a single sale here]
)

Whenever you want to populate data about sale, either ProductID or GroupProductID column will be populated depending on is that sale for a product or GroupProduct.

6
  • Good Answer, except that you do not address the point of how to represent selling the single products and/or the group product. Sep 28, 2017 at 20:47
  • @BasilBourque - I have edited my answer and added ProductSale table DDL. Sep 28, 2017 at 22:13
  • @ArtashesKhachatryan i had Create two table Product table which store like single products Products ( ProductID INT IDENTITY PRIMARY KEY, ProductName VARCHAR(50), GroupProductID INT FK );Products ( ProductID INT IDENTITY PRIMARY KEY, ProductName VARCHAR(50), GroupProductID INT FK );
    – user35662
    Oct 1, 2017 at 15:42
  • 2nd table :-->GroupProducts ( GroupProductID INT IDENTITY PRIMARY KEY, GroupProductName VARCHAR(50) }
    – user35662
    Oct 1, 2017 at 16:00
  • if Products Table ’s column id {GroupProductID } is null then it is single product
    – user35662
    Oct 1, 2017 at 16:01
2

The term "in order" is, at best, confusing. At worst, it means that you have a fundamental misunderstanding of the relational model of data. Either that or youare planning on keeing the captured data in some non-relational database.

Having said that, let me suggest you look up Bill-of-materials database design. The BOM case is a classic one, and it's well understood. You will find that more than one table is involved, and that foreign keys establish the logical links necessary to keep track of parts.

2
  • 1
    I strongly suspect the OP meant how to include the products in an order.
    – RDFozz
    Sep 28, 2017 at 22:24
  • Good catch. I took "in order" to mean sorted in some way. Sep 29, 2017 at 11:22
0

I would definitely use two or more tables. Your products table, group table and possibly a product type table that belongs to a certain group or group type table where each group contains certain product types. Your type table(s) can be just a simple key/index view.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.