CODEWITHSUNDEEP
mysqlentity-relationship
ThomasK
ThomasK

Reputation: 2220

What foregin key relations should I use in my situation? having a hard time understand

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

Upvotes: 0

Views: 508

Answers (1)

Digital Chris
Digital Chris

Reputation: 6202

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.

Upvotes: 1

Related Questions