Can or Should an ERD Action involve more than 2 Entities?

Question

This is an problem about drawing ERD in one of my course:

A local startup is contemplating launching Jungle, a new one stop online eCommerce site.

As they have very little experience designing and implementing databases, they have asked you to help them design a database for tracking their operations.

Jungle will sell a range of products, and they will need to track information such as the name and price for each. In order to sell as many products as possible, Jungle would like to display short reviews alongside item listings. To conserve space, Jungle will only keep track of the three most recent reviews for each product. Of course, if an item is new (or just unpopular), it may have less than three reviews stored.

Each time a customer buys something on Jungle, their details will be stored for future access. Details collected by Jungle include customer’s names, addresses, and phone numbers. Should a customer buy multiple items on Jungle, their details can then be reused in future transactions.

For maximum convenience, Jungle would also like to record credit card information for its users. Details stored include the account and BSB numbers. When a customer buys something on Jungle, the credit card used is then linked to the transaction. Each customer may be linked to one or more credit cards. However, as some users do not wish to have their credit card details recorded, a customer may also be linked to no credit cards. For such transactions, only the customer and product will be recorded.

And this is the solution:

The problem is the Buys action connect with 3 others entities: Product, Customer, and Card. I find this very hard to read and understand.

Is an action involving more than 2 entities common in production? If it is, how should I understand and use it? Or if it's not, what is the better way of design for this problem?

Answer 1

While the bulk of relationships in practice are binary relationships, ternary and higher relationships are normal elements of the entity-relationship model. Some examples are supplies (supplier_id, product_id, region_id) or enrolled (student_id, course_id, semester_id). However, they often get converted into entity sets via the introduction of a surrogate identifier, due to dislike of composite keys or confusion with network data models in which only directed binary relationships are supported.

Reading cardinality indicators on non-binary relationships are a common source of confusion. See my answer to designing relationship between vehicle,customer and workshop in erd diagram for more info on how I handle this.

Your solution has some problems. First, Buys is indicated as an associative entity, but is used like a ternary relationship with an optional role. Neither is correct in my opinion. See my answer to When to use Associative entities? for an explanation of associative entities in the ER model.

Modeling a purchase transaction as a relationship is usually a mistake, since relationships are identified by the (keys of the) entities they relate. If (CustomerID, ProductID) is identifying, then a customer can buy a product only once, and only one product per transaction. Adding a date/time into the relationship's key is better, but still problematic. Adding a surrogate identifier and turning it into a regular entity set is almost certainly the best course of action.

Second, the Crow's foot cardinality indicators are unclear. It looks like customers and products are optional in the Buys relationship, or even as if multiple customers could be involved in the same transaction. There are three different concepts involved here - optionality, participation and cardinality - which should preferably be indicated in different ways. See my answer to is optionality (mandatory, optional) and participation (total, partial) are same? for more on the topic.

• A card is optional for a purchase transaction. From the description, it sounds as if cards may participate totally, meaning we won't store information about a card unless it's used in a transaction. Furthermore, only a single card can be related to each transaction.

• A customer is required for a purchase transaction, and it sounds like customers may participate totally, meaning we won't store information about customers unless they purchase something. Only a single customer can be related to each transaction.

• Products are required for a purchase transaction, and since we'll offer products before they're bought, products will participate partially in transactions. However, multiple products can be related to each transaction.

I would represent transactions for this problem with something like the following structure:

I'm not saying converting a ternary or higher relationship into an entity set is always the right thing to do, but in this case it is.

Physically, that would require two tables to represent (not counting Customer, Product, Card or ProductReview) since we can denormalize TransactionCustomer and TransactionCard into Transaction, but TransactionProduct is a many-to-many relationship and requires its own table (as do ternary and higher relationships).

Transaction (TransactionID PK, TransactionDateTime, CustomerID, CardID nullable)
TransactionProduct (TransactionID PK, ProductID PK, Quantity, Price)