I'm trying to separate a weird relationship. It goes like this:
- One waiter can serve more than one kind of drink and one kind of drink can be served by more than one waiter.
- One waiter can wear more than one kind of shirt (eg different uniforms daily, or random uniform assignments, etc), and one kind of shirt can be worn by more than one waiter.
There are two many-to-many relationships here that share the waiters, but can be separately resolved. But because they share the waiters entity, then many kinds of drinks can be served while wearing many kinds of shirts.
The relational model looks like this:
My question is, how do I resolve this? It doesn't look like it lends itself easily to being decomposed into one-to-many relationships.
Edit: I changed the image to fit in better with the description, but the problem that results here is the waiter-drinks and waiter-shirt entities still exist as a result of normalizing the drinks and waiters relationship (that is many-to many) and the waiter and shirt (also many-to-many). The resulting entities then have a many-to-many relationship as waiter-drinks and waiter-shirt.
Where am I wrong?