0
$\begingroup$

I'm trying to separate a weird relationship. It goes like this:

  1. One waiter can serve more than one kind of drink and one kind of drink can be served by more than one waiter.
  2. 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:

the model

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?

Improve this question
$\endgroup$
3
  • 1
    $\begingroup$ Your diagram doesn't match your description: waiter is an entity in your description, but not in the diagram; serve and wear are relationships in your description, not in your diagram; waiter-drinks and waiter-shirt are not in your description and they are very peculiar entities anyway (judging by the attributes you mention, you may mean the serve and wear relationships). These relationships are time-bound, but your model doesn't appear to allow this. Think it through and be prepared for major changes in the model. $\endgroup$
    – reinierpost
    Feb 26, 2014 at 10:42
  • $\begingroup$ I find this comment very helpful. I'll update my diagram according to what I think would be a better way of representing the problem. $\endgroup$
    – markovchain
    Feb 28, 2014 at 7:43
  • $\begingroup$ It looks better now, but there are still issues. As far as I can see, your description just has entities 'kind of drink' (not: 'drinks'), 'waiter', and 'shirt', and two many-to-many relationships between them. Your two additional entities arise from decomposing a single many-to many relationship into two one-to-many relationships (a process known as 'entification' or 'objectification') but you're not doing this correctly and the relationship between the two intermediate entities is a mystery to me. $\endgroup$
    – reinierpost
    Mar 8, 2014 at 22:33

1 Answer 1

Reset to default
1
$\begingroup$

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.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.