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 ?
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.