many-to-manyrelational-databaseone-to-many

Is it a one-to-many or many-to-many relationship?


I was wondering whether the following would be either one-to-many or many-to-many relationship

The Users and Services tables are defined as follows:

     USERS               SERVICES
+---+------------+  +----+-------------+
|id | serviceId? |  | id | headUserId? |
+---+------------+  +----+-------------+

Where serviceId is optional and indicate in which Service the User is part of. headUserId is optionnal as well and represents which User is the head of service. The user in question does not need to be part of the Service to be the head of it, as a User can be the head of multiple Services.

So, if different Users can refer to the same Service. This is a clear one-to-many relationship (One Service -> Many Users).
However, adding the headUserId in the Services table implies that: different Services can refer to the same User.
But not because a User can be part of multiple Services, the second relation has not the same meaning.

An example would be the following:

     USERS               SERVICES
+---+-----------+  +----+------------+
|id | serviceId |  | id | headUserId |
+---+-----------+  +----+------------+
| 1 |           |  | 1  | 2          |
| 2 | 1         |  | 2  | 3          |
| 3 | 2         |  | 3  | 2          |
| 4 | 2         |  | 4  |            |
+---+-----------+  +----+------------+

User 3 and 4 refer to the Service with id 2.
Service 1 and 3 have the User with id 2 as the head of service.

For me, it looks like a weird one-to-many relationship for both sides and not a proper many-to-many relationship.

Currently, an implementation in C# works with a one-to-many relationship defined on both sides. This feels wrong and not the right way to go.

What relationship is it ? How could it be done in a clearer / easier way ?


Solution

  • A user may be part of many services. A service may be used by many users. So, first and foremost, you have a many-to-many relationship for

    service_users(service_id, user_id)

    There is no way around it, because three people may use the same service, but a person is not limited to using a single service.

    However, a service may have a headUserId, representing the head user. A user may be the head user of multiple services, but a service may have maximum one head user. Hence, you have a one-to-many relationship as well inside the service table, represented by the headUserId field.

    So the reason for the confusion is that you do not have one, but actually two relations, the first relation representing many people potentially using many services, translating to a many-to-many relationship, whereas the second relation is a one-to-many relation, representing the head user of a service.