2 foreigns keys referencing to 1 primary key (1 Viewer)

Mina Garas Daniel

Registered User.
Local time
Today, 15:21
Joined
Jul 21, 2017
Messages
66
Hello everybody

i have a question

what i know the best practices 1 pk link to 1 fk

can i link a primary key in a table with 2 foreign keys in two different tables
and build queries and forms on that
without any bugs in database after that



thanks
 

June7

AWF VIP
Local time
Today, 05:21
Joined
Mar 9, 2014
Messages
5,425
Yes, a PK can be saved into multiple related tables.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:21
Joined
Oct 29, 2018
Messages
21,358
Hi. If you show us what you have in mind, we can clarify it for you. Just a thought...
 

Mina Garas Daniel

Registered User.
Local time
Today, 15:21
Joined
Jul 21, 2017
Messages
66
Hi. If you show us what you have in mind, we can clarify it for you. Just a thought...

Dear
am trying to make a database handle and management recipes
my database has 3 tables
one table contain recipes itself ingredients and quantities
another table contain steps to prepare each item
last table contain historical data about each recipe like
creation date, recipe by,.... etc

another important point
if i will change or update any thing in recipes table or steps
i need to save old records before change any thing

as following tables contents :
Tbl01-ItemName (Short name IP)
IPPK- autonumber
Recipe Date- date
Recipe Type-short text (Original-update)
Recipe by-short text
What update ?-short text
Why update ?-short text
IP Code-short text
IP Name-short text
Kitchen-short text

Tbl02-Recipe
RecipePK- autonumber
IPFK- number
RawCode- text
Unit- text
qty-number

Tbl03-IPSteps
IPFK- number
Main Category-short text
StepsNo-Number
Category-short text
Steps- short text
Pics- short text (Image path)

when i try to create forms based on
this tables by using tabs in main form
one of subform not working and fk not appear in it

that's all
thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:21
Joined
Oct 29, 2018
Messages
21,358
Dear
am trying to make a database handle and management recipes
my database has 3 tables
one table contain recipes itself ingredients and quantities
another table contain steps to prepare each item
last table contain historical data about each recipe like
creation date, recipe by,.... etc

another important point
if i will change or update any thing in recipes table or steps
i need to save old records before change any thing


...


when i try to create forms based on
this tables by using tabs in main form
one of subform not working and fk not appear in it

that's all
thanks
Hi. Can you also show us a screenshot of your Relationship Window showing how your tables are linked together? Thanks.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:21
Joined
May 21, 2018
Messages
8,463
I do not know if it is too late, but having spaces in names and special characters is asking for a lot of unnecessary pain. I would fix now before it is too late. This will fail in a lot of queries and vba code.

Recipe Date ' should be RecipeDate or Recipe_Date
Why update ? ' should be WhyUpdate or Why_Update
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:21
Joined
Feb 28, 2001
Messages
27,001
Particularly in the case where you have, say, one recipe name and a list of ingredients and a separate list of steps and a separate historical sequence, there is absolutely nothing wrong in taking the PK of the recipe name and making it act as a foreign key in the tables related to the recipe.

There IS, however, a potential "GOTCHA" in that without a constraint between, say, the ingredients list and the list of preparation steps, you run a risk of creating a Cartesian (i.e. multiplicative or permutation) JOIN between the two child tables. You end up with more joined records than you needed or wanted.

Which is why I might suggest that instead of having steps and ingredients, you combine the steps in such a way that you can't get confused by what to do with each ingredient. I.e. the ingredients table not only tells you what you must have, but what to do with it when you have it IN THE SAME TABLE. Then you can add an order of application as a second number in the table that allows you to sort by "usage order."
 

Mina Garas Daniel

Registered User.
Local time
Today, 15:21
Joined
Jul 21, 2017
Messages
66
Particularly in the case where you have, say, one recipe name and a list of ingredients and a separate list of steps and a separate historical sequence, there is absolutely nothing wrong in taking the PK of the recipe name and making it act as a foreign key in the tables related to the recipe.

There IS, however, a potential "GOTCHA" in that without a constraint between, say, the ingredients list and the list of preparation steps, you run a risk of creating a Cartesian (i.e. multiplicative or permutation) JOIN between the two child tables. You end up with more joined records than you needed or wanted.

Which is why I might suggest that instead of having steps and ingredients, you combine the steps in such a way that you can't get confused by what to do with each ingredient. I.e. the ingredients table not only tells you what you must have, but what to do with it when you have it IN THE SAME TABLE. Then you can add an order of application as a second number in the table that allows you to sort by "usage order."

thank for explanation
but how can i merge recipe and steps tables in one table
each table contains completely different data
i you have an idea please tell me thanks
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:21
Joined
Feb 28, 2001
Messages
27,001
Table tSteps:
Field: RecipeID, number, FK (to RecipeName), part of compound PK for this table
Field: UseOrder, Ordinal number showing what you use first, part of compound PK
Field: ItemID, FK to tPantry (table listing what is available)
Field: Amount, number
Field: AmtUnit, text
Field: Action, short text

Table tPantry
Field: ItemID, PK (here), FK (in tSteps)
Field: ItemName, short text, the name of the item
Field: ItemDescr, short text, more about the item (if more is needed)

So your recipe #32 might have ingredients listed. Let's say (for no good reason) that item 76 is a skillet and item 95 is butter. Item 104 is flour.

Code:
32, 1, 76, 1, each, place on high heat
32, 2, 95, 1, pat, melt
32, 3, 104, 0.5, cup, brown lightly for 2 minutes

Now of course you would write queries to do this in proper order, and you would JOIN the pantry table to see names and not show the recipe nuimber. That way, you would see only these things from the query:

Code:
1, skillet, 1, each, place on high heat
2, butter, 1, pat, melt
3, flour, 0.5, cup, brown lightly for 2 minutes

If you need more flour or more butter later, you just make it another line item and describe what you do with the extra material.

I'm not saying you should do this... unless it makes sense to you. And if you see a slight variant of this, of course make it your own. However, this way, your ingredients are tied to the actions to be done with them. If you DIDN'T do that, your would get a total of nine combinations out of three items and three actions. So when I said to establish a constraint to avoid the permutation JOIN, it was something like the above that I had in mind. In this case, the action includes a subject material. You still have three tables, but there is that added PK/FK constraint between the actions and the materials in your pantry or kitchen.
 

Users who are viewing this thread

Top Bottom