0

I'm having a hard time understanding when to use identifying or non-identifying relationships. I've read some answers here on SO, but I'm still confused. I guess I'm just that slow...

So I've included a picture - of a small part - of my ER diagram that contains drink recipes:

ER Diagram

My obvious question is; Which of these relations, if any, should be identified? - and why?

As you can see; I only have one-to-many non-identifying relationship - which makes me think I must be doing something wrong - but it works though.

The relation between glassware and recipes for instance:
Each recipe requires a glass - which means a recipe cannot exist without a glass - I guess. So I've set recipes.fk_glassware_id to NN, and the given id must correspond to an id in the glassware-table. (one glass can span over multiple recipes, therefore one-to-many). But should it be a identified relationship?

What about the relations between recipes_rel_tags and recipes- and tags-tables. Should any of those be identified relations? No entry in this table can exist without any of the linked tables..

EDIT:
I've added a new picture of my diagram. Now the fk_'s in my _rel_-tables looks like primarykeys - all of them - when I changed the relationships to identified.
What does that mean?

ER Diagram 2

ThomasK
  • 2,210
  • 3
  • 26
  • 35

1 Answers1

1

I'm not sure if you've seen the answer to this question: What's the difference between identifying and non-identifying relationships? . I'm not sure I can do any better than that, but maybe I can make it more specific to your example.

The way to think about an identifying relationship is to ask yourself: would I EVER want to create the child separate from the parent? If you would, it's not an identifying relationship. The answer I referenced uses the person to phone-number relationship. You wouldn't create a bunch of phone numbers then LATER tie them to people (at least not in a usual use case). In your case, you might create recipes and decide what the best glassware for them is later (which would make it an optional non-identifying relationship), or you might demand that some glassware be chosen on create (mandatory non-identifying relationship). However you might also later add to your glassware table (say... fluted and stemless wine glasses) and then change some recipes to use them instead of standard wine glasses... so it's definitely NOT an identifying relationship.

You also ask:

What about the relations between recipes_rel_tags and recipes- and tags-tables. Should any of those be identified relations? No entry in this table can exist without any of the linked tables..

Right! You would NEVER create a record in recipes_rel_tags without records in recipes and tags. Therefore it has 2 identifying relationships.

Community
  • 1
  • 1
Digital Chris
  • 6,177
  • 1
  • 20
  • 29
  • So I changed the relationships to `identifying`. Now it looks like I'm having more PK's.. I might have misunderstood.. I added a new picture to my first post. – ThomasK Jan 08 '14 at 20:23
  • That is correct. If you want them to be identifying, it becomes part of the composite primary key. In common practice, people often just create foreign keys in this case. You are now deep into "technically correct" territory, and this is a detail many database designs don't even consider. – Digital Chris Jan 08 '14 at 20:35
  • So; in this case I could leave out the `id`'s in these `_rel_`-tables? - and query a spesific row based on a queryvalue equal to both foregin keys combined? If so. How does a SQL statement look like then? – ThomasK Jan 08 '14 at 20:43
  • No, the table looks right as it is. Those `id`s together form a [compound key](http://en.wikipedia.org/wiki/Compound_key) – Digital Chris Jan 08 '14 at 20:56
  • I have to look more into this.. I thought there could be only one instance of the same `PK`. `fk_recipes_id` and `fk_tags_id` inside `recipes_rel_tags`-table will occour multiple times in my case - but they won't if they're read together (compound) by the database/table though.. – ThomasK Jan 08 '14 at 21:13
  • Yes, a compound primary key will allow (1,1) and (1,2) and (2,2) but error if you try to add another (1,1) – Digital Chris Jan 08 '14 at 21:22
  • I can see how this can prevent duplications and such.. I think I understand more how this works now. Thanks for taken the time to explain.. – ThomasK Jan 08 '14 at 21:46