Designing a join table for max n records

Question

I know that a basic rule for designing a relational dataset is to not do things like this:

create table Order (
    OrderID int indentity(1, 1) not null, -- PK
    CustomerID int identity(1, 1) not null, -- FK to Customer table
    ProductID1 int not null, -- FKs to Product table
    ProductID2 int not null, 
    ProductID3 int not null,
    ...
    ProductID10 int not null,
    primary key (OrderID)
)

Because it results in problems like:

• What if the customer orders more than 10 products in a single order? This can't support that.
• What if the vast majority of customers only ever order 1 or 2 products? That's a lot of surplus fields.

And the correct answer of course is to normalize:

create table Order (
    OrderID int identity(1, 1) not null, -- PK
    CustomerID int not null -- FK to Customer,
    primary key (OrderID)
)

create table OrderProduct (
    OrderID int not null, -- FK to Order table
    ProductID int not null, -- FK to Product table
    Quantity int not null,
    primary key (OrderID, ProductID)
)

However, what about in cases where there is a known maximum or range of the number of Products that can be associated with a single Order?

For example, where there is a strict limit of 10 Products in an Order, and/or a minimum of 2 Products in an Order?

This normalized structure does not set limits on how many records per Order can be created in OrderProduct - an Order could be created with 0 associated OrderProducts, or 200.

Is there a standard approach to handle this kind of requirement in the database design, or is it something that can only be handled in the program code whilst inserting records?

Answer 1

In that case the application should manage the amount of products on each order.

If you want to enforce a limit on database level you can add a column ProductOrderPosition in OrderProduct that's the ordering position of each product by each order, and you can add a CHECK constraint to limit this value, for example:

ALTER TABLE OrderProduct ADD CONSTRAINT CHK_OrderProduct_ProductOrderPosition 
    CHECK (ProductOrderPosition <= 10)

If you don't want additional columns you could validate with an after insert trigger, checking that each order doesn't have more than X products.

CREATE TRIGGER dbo.utrOrderProductCheckAmount ON OrderProduct AFTER INSERT
AS
BEGIN

    DECLARE @v_ProductsByOrderLimit INT = 10

    IF EXISTS (
        SELECT
            'more than allowed!'
        FROM
            OrderProduct AS O
            INNER JOIN (SELECT DISTINCT I.OrderID FROM inserted AS I) ON O.OrderID = I.OrderID
        GROUP BY
            O.OrderID
        HAVING
            COUNT(1) > @v_ProductsByOrderLimit
        )
    BEGIN

        ROLLBACK

        RAISERROR('Maximum products by order limit reached.', 11, 1)

    END

END

You definitely don't want to store pivoted Products IDs on the Order table for the reasons you mentioned before. I'll add another reason, what happens if you want to know in how many orders does a product appear? You need 10 poorly indexed joins, instead of 1 optimized SELECT against OrderProduct.