When selecting an order you would select from Order and use an INNER JOIN to OrderDetail on Order.OrderID - OrderDetail.OrderID. You would also use a LEFT OUTER JOIN to BucketDetail on OrderDetail.OrderDetailID = BucketDetail.OrderDetailID and another LEFT OUTER JOIN to BoxDetail on OrderDetail.OrderDetailID = BoxDetail.OrderDetailID. Depending on the product type one of the LEFT OUTER JOINs will fail and one will succeed. The OrderDetail.ProductType column will tell you which of the joins should have worked, which in turn tells you which columns you are expecting to find information in, either BucketDetail columns or BoxDetail columns.

Depending on your application you might do that as all one join as above, or just go as far as the OrderDetail table until the need for more information comes up - in which case you can use the ProductType column to know which table to go look for that information.

So instead of having mutually exclusive columns you would have mutually exclusive tables. A specific OrderDetailID would only be found in one of the sub tables and not the others.

Hopefully that is clearer.