Resolving many to many relationships? 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?
If i rightly assume that you are trying to convert the E-R diagrams to relations (tables), then the procedure to convert a many-to-many relationship into relations is to create one more table, apart from the 2 tables that you will create for the 2 entities (waiter-drinks and waiter-shirt). This new table created for the relationship will have its attributes (columns) as the PKey of the constituting entities. In your case, this table will contain 3 attributes; namely waiter_number, drink_number and shirt_number.

Had you been working on a one-to-one or one-to-many relationship, you would augment one more column to the table on many sides instead of creating another relation.