Database design - composite key relationship issue

Question

I had posted a similar question before, but this is more specific. Please have a look at the following diagram: The explanation of this design is as follows:

• Bakers produce many Products
• The same Product can be produced by more than one Baker
• Bakers change their pricing from time-to-time for certain (of their) Products
• Orders can be created, but not necessarily finalised

The aim here is to allow the store manager to create an Order "Basket" based on whatever goods are required, and also allow the system being created to determine the best price at that time based on what Products are contained within the Order.

I therefore envisaged the ProductOrders table to initially hold the productID and associated orderID, whilst maintaining a null (undetermined) value for bakerID and pricingDate, as that would be determined and updated by the system, which would then constitute a finalised order.

Now that you have an idea of what I am trying to do, please advise me on how to to best set these relationships up.

Thank you!

Answer 1

If I understand correctly, an unfinalised order is not yet assigned a baker / pricing (meaning when an order is placed, no baker has yet been selected to bake the product).

In which case, the order is probably placed against the Products Table and then "Finalized" against the BakersProducts table.

A solution could be to give ProductsOrders 2 separate "ProductID's", one being for the original ordered ProductId (i.e. Non Nullable) - say ProductId, and the second being part of the Foreign key to the assigned BakersProducts (say ProductId2). Meaning that in ProductsOrders, the composite foreign keys BakerId, ProductId2 and PricingDate are all nullable, as they will only be set once the order is Finalized.

In order to remove this redundancy, what you might also consider is using surrogate keys instead of the composite keys. This way BakersProducts would have a surrogate PK (e.g. BakersProductId) which would then be referenced as a nullable FK in ProductsOrders. This would also avoid the confusion with the Direct FK in ProductsOrders to Product.ProductId (which from above, was the original Product line as part of the Order).

HTH?

Edit:

CREATE TABLE dbo.BakersProducts
(
  BakerProductId int identity(1,1) not null, -- New Surrogate PK here
  BakerId int not null,
  ProductId int not null,
  PricingDate datetime not null,
  Price money not null,
  StockLevel bigint not null,

  CONSTRAINT PK_BakerProducts PRIMARY KEY(BakerProductId),
  CONSTRAINT FK_BakerProductsProducts FOREIGN KEY(ProductId) REFERENCES dbo.Products(ProductId),
  CONSTRAINT FK_BakerProductsBaker FOREIGN KEY(BakerId) REFERENCES dbo.Bakers(BakerId),
  CONSTRAINT U_BakerProductsPrice UNIQUE(BakerId, ProductId, PricingDate) -- Unique Constraint mimicks the original PK for uniqueness ... could also use a unique index
)

CREATE TABLE dbo.ProductOrders
(
  OrderId INT NOT NULL,
  ProductId INT NOT NULL, -- This is the original Ordered Product set when order is created
  BakerProductId INT NULL, -- This is nullable and gets set when Order is finalised with a baker
  OrderQuantity BIGINT NOT NULL,


  CONSTRAINT FK_ProductsOrdersBakersProducts FOREIGN KEY(BakersProductId) REFERENCES dbo.BakersProducts(BakerProductId)
  .. Other Keys here
)