enter image description here

Here a customer can submit an order for one or many products . so, there is a one to many relation between customer_order and customer_ordered_products entities. Inside customer_ordered_products a product is uniquely distinguished by two attributes (product_category and product_id ). product_category is simply name of one of the five entities (sports_equipments, appliances, clothes, electronics, office_supplies ) representing different products. So, for each entry in customer_ordered_products I can have maximum one product_id and one product_category . But as only one product_id from the five entities can be present in the customer_ordered_products entity, so that relation is many to zero or one.

The problem is, when I think all of the zero to one relation to be zero there would be no product_id & when I consider two or more of them to be one(1) then there would be multiple values for a single valued attribute .

In this diagram I could use a product entity instead of five entities having product_category distinguishing different categories .That would solve the problem. But I do want to implement this diagram with different entities for different categorized products and solve this problem too.

  • Is there a reason for splitting the "product" entity into 5 entities? Why not just 1? – ypercubeᵀᴹ Nov 13 '17 at 9:26

The telling part is where you said "But as only one product_id from the five entities can be present in the customer_ordered_products"

I could fix this by putting a product_category on the customer_order (a "default" if you will) and add a constraint between customer_ordered_products.product_category and customer.product_category that ensures they are the same value.

Another way to do it, if you can, is to remove the product_category from the customer_ordered_products and simple join customer_orders to customer_ordered_products everytime you needed to get that 1 applicable value of product_category.

Hope this helps.

| improve this answer | |
  • I can't add attribute product_category to customer_ordered_products as one order can have multiple products from multiple categories . That's why customer_order & customer_ordered_products are related by one to many . – Utshaw Nov 14 '17 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.