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 ?
Rekwass is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.