locked
Database design and unique constraints RRS feed

  • Question

  • Hi all, 

    Appreciate some assistance with my database design.

    My database will hold orders for products which are tracked by serial number. The serial number must be unique by product and i'd like to implement this constraint in the database. My serial number table does not include the product & I don't think it should  but perhaps I need to if i want this constraint. 

    My tables will look something like this. 

    CREATE TABLE Product (
    	ProductID INT IDENTITY PRIMARY KEY
    );
    
    CREATE TABLE OrderLine (
    	OrderLineID BIGINT IDENTITY PRIMARY KEY,
    	ProductID INT CONSTRAINT FK1 REFERENCES Product(ProductID),
    	Quantity INT
    );
    
    CREATE TABLE OrderedSerialNumber (
    	SerialNumber VARCHAR(30),
    	OrderLineID BIGINT CONSTRAINT FK2 REFERENCES OrderLine(OrderLineID),
    	CONSTRAINT PK PRIMARY KEY
    	(
    		OrderLineID,
    		SerialNumber
    	)
    );
    
    

    So that I can implement my unique constraint (i.e. unique index on ProductID, SerialNumber), do I need to add ProductID to OrderedSerialNumber? It feels wrong? But i'm not trained in this - appreciate your help.

    Thanks.


    Thursday, October 20, 2016 5:01 AM

Answers

  • Let me try to put this a different way.

    Products have serial numbers.   When the product is ordered, the order needs to reference the product serial number.  The order does not "own" the serial number.

    I would create a ProductSerialNumber table, and have Orders reference that table.

    CREATE TABLE [dbo].[Product]
    ([ProductID] [INT] IDENTITY(1, 1) NOT NULL,
     PRIMARY KEY CLUSTERED([ProductID] ASC)
    );
    GO
    
    CREATE TABLE [dbo].[ProductSerialNumber]
    ([ProductSerialNumberID] [INT] IDENTITY(1, 1) NOT NULL,
     [ProductID]             [INT] NOT NULL,
     [SerialNumber]          [VARCHAR](30) NOT NULL,
     CONSTRAINT [PK__ProductSerialNumber] PRIMARY KEY CLUSTERED([ProductSerialNumberID] ASC),
     CONSTRAINT [UN_ProductSerialNumber] UNIQUE NONCLUSTERED([ProductID] ASC, [SerialNumber] ASC)
    );
    GO
    
    ALTER TABLE [dbo].[ProductSerialNumber]
    WITH CHECK
    ADD CONSTRAINT [FK_ProductSerialNumber_Product] FOREIGN KEY([ProductID]) REFERENCES [dbo].[Product]([ProductID]);
    GO
    
    ALTER TABLE [dbo].[ProductSerialNumber] CHECK CONSTRAINT [FK_ProductSerialNumber_Product];
    GO
    
    CREATE TABLE [dbo].[OrderLine]
    ([OrderLineID]           [BIGINT] IDENTITY(1, 1) NOT NULL,
     [ProductID]             [INT] NULL,
     [ProductSerialNumberID] [INT] NULL,
     [Quantity]              [INT] NULL,
     CONSTRAINT [PK__OrderLine] PRIMARY KEY CLUSTERED([OrderLineID] ASC)
    );
    GO
    
    ALTER TABLE [dbo].[OrderLine]
    WITH CHECK
    ADD CONSTRAINT [FK_OrderLine_ProductSerialNumber] FOREIGN KEY([ProductSerialNumberID]) REFERENCES [dbo].[ProductSerialNumber]([ProductSerialNumberID]);
    GO
    
    ALTER TABLE [dbo].[OrderLine] CHECK CONSTRAINT [FK_OrderLine_ProductSerialNumber];
    GO
    
    ALTER TABLE [dbo].[OrderLine]
    WITH CHECK
    ADD CONSTRAINT [FK1] FOREIGN KEY([ProductID]) REFERENCES [dbo].[Product]([ProductID]);
    GO
    
    ALTER TABLE [dbo].[OrderLine] CHECK CONSTRAINT [FK1];
    

    • Marked as answer by clay123123123 Sunday, October 23, 2016 10:51 PM
    Friday, October 21, 2016 3:05 PM
    Answerer

All replies

  • Consider the following enhancement to your initial design:

    CREATE TABLE Product (
    	ProductID INT IDENTITY (1,1) PRIMARY KEY,
    	ProductName nvarchar(255) NOT NULL UNIQUE,
    	ProductDesc nvarchar(max) NOT NULL,
    	ModifiedDate datetime default (getdate())
    );
    
    CREATE TABLE Customer (
    	CustomerID INT IDENTITY (1,1) PRIMARY KEY,
    	CustomerName nvarchar(255) NOT NULL UNIQUE,
    	CustomerDesc nvarchar(max) NOT NULL,
    	CustomerAddress nvarchar(255),
    	ModifiedDate datetime default (getdate())
    );
    
    CREATE TABLE ProductOrder (
        OrderID  INT IDENTITY (1,1) PRIMARY KEY,
    	SerialNumber VARCHAR(30),
    	CustomerID INT REFERENCES Customer(CustomerID),
    	ModifiedDate datetime default (getdate())
    );
    
    CREATE TABLE ProductOrderDetail(
        OrderID INT REFERENCES ProductOrder(OrderID),
    	ProductOrderLineID BIGINT IDENTITY (1,1) PRIMARY KEY,
    	ProductID INT REFERENCES Product(ProductID),
    	Quantity INT,
    	ModifiedDate datetime default (getdate()),
    	UNIQUE(OrderID,ProductID)
    );
    
    



    Kalman Toth Database & OLAP/AI Architect SQL Server 2016 Database Design
    New Book : Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016


    • Proposed as answer by AV111 Friday, October 21, 2016 2:53 PM
    Friday, October 21, 2016 2:41 PM
  • Let me try to put this a different way.

    Products have serial numbers.   When the product is ordered, the order needs to reference the product serial number.  The order does not "own" the serial number.

    I would create a ProductSerialNumber table, and have Orders reference that table.

    CREATE TABLE [dbo].[Product]
    ([ProductID] [INT] IDENTITY(1, 1) NOT NULL,
     PRIMARY KEY CLUSTERED([ProductID] ASC)
    );
    GO
    
    CREATE TABLE [dbo].[ProductSerialNumber]
    ([ProductSerialNumberID] [INT] IDENTITY(1, 1) NOT NULL,
     [ProductID]             [INT] NOT NULL,
     [SerialNumber]          [VARCHAR](30) NOT NULL,
     CONSTRAINT [PK__ProductSerialNumber] PRIMARY KEY CLUSTERED([ProductSerialNumberID] ASC),
     CONSTRAINT [UN_ProductSerialNumber] UNIQUE NONCLUSTERED([ProductID] ASC, [SerialNumber] ASC)
    );
    GO
    
    ALTER TABLE [dbo].[ProductSerialNumber]
    WITH CHECK
    ADD CONSTRAINT [FK_ProductSerialNumber_Product] FOREIGN KEY([ProductID]) REFERENCES [dbo].[Product]([ProductID]);
    GO
    
    ALTER TABLE [dbo].[ProductSerialNumber] CHECK CONSTRAINT [FK_ProductSerialNumber_Product];
    GO
    
    CREATE TABLE [dbo].[OrderLine]
    ([OrderLineID]           [BIGINT] IDENTITY(1, 1) NOT NULL,
     [ProductID]             [INT] NULL,
     [ProductSerialNumberID] [INT] NULL,
     [Quantity]              [INT] NULL,
     CONSTRAINT [PK__OrderLine] PRIMARY KEY CLUSTERED([OrderLineID] ASC)
    );
    GO
    
    ALTER TABLE [dbo].[OrderLine]
    WITH CHECK
    ADD CONSTRAINT [FK_OrderLine_ProductSerialNumber] FOREIGN KEY([ProductSerialNumberID]) REFERENCES [dbo].[ProductSerialNumber]([ProductSerialNumberID]);
    GO
    
    ALTER TABLE [dbo].[OrderLine] CHECK CONSTRAINT [FK_OrderLine_ProductSerialNumber];
    GO
    
    ALTER TABLE [dbo].[OrderLine]
    WITH CHECK
    ADD CONSTRAINT [FK1] FOREIGN KEY([ProductID]) REFERENCES [dbo].[Product]([ProductID]);
    GO
    
    ALTER TABLE [dbo].[OrderLine] CHECK CONSTRAINT [FK1];
    

    • Marked as answer by clay123123123 Sunday, October 23, 2016 10:51 PM
    Friday, October 21, 2016 3:05 PM
    Answerer