Suppose you have Item (Article ID, item_name) and Order (Request ID, user_id, order_date) entities, with primary keys denoted in bold. Because an order can consist of several items, and one item can be present in several orders, there is a many-to-many relationship between these entities that is normally divided by inserting an additional associative entity between them, in this case Article – Order (Article ID, Request ID, item_count) with an additional field, and dividing the many-to-many relationship into two one-to-many relationships.
So far we have the following:
- Item (Article ID, Article name)
- Order (Request ID, user_id, order_date)
- Article – Order (Article ID, Request ID, item count)
Suppose further that a user can review each item received from their order. Is it advisable to create the following additional entity:
- revision (Article ID, Request ID, grade, comment_content, comment_date),
or it would be more usual to subsume it under Article-Order, in which case we would obtain the following:
- Article – Order (Article ID, Request ID, item_count, grade, comment_content, comment_date)?
Of course, the latter would allow the possibility of NULL, and for me it does not seem completely conceptual.
Would the first option be the best option if we had to generate an additional attribute, id_review, and declare it as the main key in the revision relationship?