Double binary relationship with weak entity vs ternary relationship

Question

I have a problem that states that a group of servers send daily data through a group of data lines. Each server has an id and each data line has an id. I've come up with a couple of ideas but I'm not sure which is better. From what I've read it's always preferable to avoid ternary relationships when something can be done in terms of binary relationships. But in my case I came up with the following binary relationship

(Server)[serverId]--1--------1..* --(DailyData)[date]--1..*----------1--(DataLine)[dataLineId]

where DailyData is weak towards Server, as many servers will have reports on the same date. In the end, I would get a Server table with (serverId) as PK, a DailyData table (serverId, date) with a combination of both as PK, and a DataLine table (serverId, date, dataLineId) with a combination of the three as PK. This (if I did it correctly) seems to work.

My other idea is to use a ternary relationship between Server, Date and DataLine, which seems more elegant and has no weak entities. The relationship table would just be (serverId, dataLineId, date) with the three of them serving as PK, which kind of arrives at the same conclusion as my other solution, but in a more elegant and straightforward way.

Which would be a preferable solution?

Answer 1

The kind of relationship that you describe would in UML be be an association class: DailyData makes no sense without a Server and a Dataline:

An association class is at the same time a binary association and a class. An instance thereof cannot exist without a linked server and a linked data line, and this is exactly what you try to model.

The {id} in UML indicates that an attribute is part of the identifier of the class. It's close to the concept of primary key, although UML defines no further its semantics. The Date would be part of the identifier of DailyData as it would allow to distinguish between several dates for the same server and data line.

To implement an association-class in a database, you would typically use an association table, also known as join-table. Since you speak of "weak" and other concepts that are not UML, I'd add that in an ERD, the closest to an association class is an "associative entity".

There is by the way no such thing as a weak entity in UML. The closest is a composite aggregation (black diamond), as the life cycle of the weak entity depends on the life cycle of its owner. It is however not allowed in UML to have the same instance being component of two different composite aggregations. So in UML, the association class would be the best option for your problem.