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 Product
s 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?
Best Answer
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
inOrderProduct
that's the ordering position of each product by each order, and you can add aCHECK
constraint to limit this value, for example: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.
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
againstOrderProduct
.