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
For example, where there is a strict limit of 10
Products in an
Order, and/or a minimum of 2
Products in an
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?