ERD and Database Relationship issue

[DevTycoon]

I am trying out the ERD for a recipe database and can not get the relationships I should be defining on this model. I get an error when I try to have referential integrity between the recipeID in the recipe_steps table and the recipe_steps_ingredients table.

Recipe ERD
http://www.databaseanswers.org/data_models/recipes/index.htm

Any suggestions on how I may do this? My sample database is attached. Thanks!

 

[jdraw]

See the revised model in the attached.

You need both Recipe and Step relationships.

 

[DevTycoon]

See the revised model in the attached.

You need both Recipe and Step relationships.

Ok that seems too easy HAHA. I still don't understand how you got the referential integrity to work. Do you have to link both of them without referential integrity first?

Also, I do not understand this type of relation. I am so used to having a primary key ID and just indexing the fields that I want uniqueness in. Is there a way I can learn more about this type of relation?

 

[jdraw]

Research Junction Table or Access Many to Many Relationship

Here's a youtube video that should help.

Good luck

 

[DevTycoon]

Research Junction Table or Access Many to Many Relationship

Here's a youtube video that should help.

Good luck

I have see that vid before... It is good stuff. I guess my issue is that the dual relation from the junction table is confusing me. It seems that if there is a many to many relationship with two tables that have a compound primary key between them there needs to be multiple relations made. Is that a safe generalization?

Also,

I deleted the relationship you made and tried to reproduce it and still get the error when I check referential integrity. I made a relationship in the edit relationships box after I dragged recipe_id from recipe_step_ingredient to recipe_steps and selected both fields(recipe_id and step_number) from both tables and checked referential integrity. The error says No unique index found. Am I doing this wrong?

 

[GinaWhipp]

The error says No unique index found.

When enforcing referential integrity one of the fields must be a primary key.

 

[jdraw]

Yes you did it wrong.

When you have a compound PK, things get tricky when making relationships with other tables. You have to allow for all components of the compound key. I prefer to make a single field(surrogate) PK in such set up, and then make a compound unique index on the "original fields making up the proposed compound PK" -- this index is to prevent duplicates.

I saw in your model a 1 to 1 relationship between recipe step and recipe step ingredient. In my view it should have been 1 to many, but you have a compound PK so you need both components in the relationship. I edited the relationship to include the Step from each table in the relationship, then clicked the box to enforce referential integrity.

You can post your latest version if you like, and I'll take a look.

I see Gina has responded while I was typing. I agree with her statement, but you have a compound PK and the whole PK must be unique and participate in relationship to enforce referential integrity.

 

[GinaWhipp]

Junction tables...

In a normalized database you do not duplicate information in the *primary* tables. In this example the primary tables are...

tblStudents
sStudentID

tblClasses
cClassID

The Students table list every Student and the Classes table lists all the Classes that are available.

Since one Student will take many different Classes. Each Student will take different Classes and I have 10 Students. I must create a Junction table to enroll the Students in the Classes that they are going to take.

tblStudentClasses
scStudentID (relate to tblStudents)
scClassID (relate to tblClasses)

Make sense now? Oh, and here's a large scale example of why Junction tables are needed (used)...
http://www.access-diva.com/dm7.html

Take a look at the Business Rules section.

 

[GinaWhipp]

@jdraw

Great minds think alike... I see our posts are hitting at the same time!

 

[jdraw]

@Gina,

Right. I was trying to figure out how we went from recipes to students and classes. The OP got the data model from databaseanswers. It has a compound PK and when creating a relationship both parts of the compound PK had to be in that relationship.

I was just trying to give info/links on junction tables and many to many relationship.

Looks like the OP has left the discussion.

 

[GinaWhipp]

@jdraw

I tend to use Students and Classes because I think it's easier to *visualize* Junction table uses (needs) but I could be wrong.

Oh, no worries, OP will be back, I think there are two (or maybe three) threads they've opened.

 

[DevTycoon]

@Group
I left discussion to return some bad bagels ...Bought them yesterday and they were already turning today.

@jDraw
I got that to work now. I had to pull the linking names from the Recipe_Steps table to the Recipe_Step_Ingredients table

@GinaWhipp

I have seen that website before. The relations are daunting but I want to learn proper normalization and appreciate the link. I hid some of the tails on those tables to see what you are saying about business rules. If I translate that back to the ingredients...I think I have the following

Recipes will have ingredients (1 - many)

Steps use ingredients (1 - many)

Is the table structure set up to accommodate multiple ingredients per step and also prevent duplicate ingredients for a specific step? I do not see that being possible at the current structure I think. If I used a surrogate key here it would just be a free for all ... Right?

Ingredients will have zero to many recipe applications

Recipes and Ingredients are my main tables.

Thanks for all the discussion and links today.

 

[GinaWhipp]

The way jdraw set it up you can have multiple ingredients per step. All other items you listed appears fine to me.

Side note: I think I'll build my own model for this and add to my site, looks like fun!

 

[jdraw]

@DevTycoon

Glad you have it working.

@Gina,
Adding model to your site sounds a good idea.

Good Night All.

 

[DevTycoon]

I thank both of you again.


On a side note I think it would be fun to get this up and running some how so I can select ingredients I want to use and query the database to recommend recipes. I'm not sure how I would do that now but will make that a future goal.

 

[GinaWhipp]

So here I am looking at this database becasue I decided to start a Data Model and I noticed a problem. You cannot join Autonumber to Autonumber as PK's it won't work. You are going to add steps which means duplicate FK's in the Recipe_Steps table, no way you will be able to do so Autonumber to Autonumber as you cannot update that field and labeling it as a PK will prevent it. (Well, even as a FK it will not work). You can only relate Autonumber to Long.

 

[GinaWhipp]

F.Y.I., just posted three new Data Models...

Recipes
http://www.access-diva.com/dm15.html

Groceries
http://www.access-diva.com/dm16.html

Recipes with Grocery List
http://www.access-diva.com/dm17.html

Enjoy!