I am working on constructing a database of prepared foods and their ingredients. It contains one table listing ingredients, each with a code number (1 chicken, 2 rice, 3 tomato etc.). It contains another table listing the foods, each with a code number (1 chicken soup, 2 lasagna, 3 chocolate cake etc.). I want to be able to use queries to create lists of ingredients in each food (e.g. find all the ingredients in chocolate cake), and I also want to be able to use queries to search for foods that contain or do not contain certain ingredients (e.g.find all the foods that contain tomato). So far I have completed the list of ingredients. How can I set up the list of foods so it will work the way I want it to? I am stuck because the two tables can only be linked on one field. Anybody have an idea for me?
I'm using Base 2.3.1 and Windows XP
Thanks very much!
Elizabeth
[Solved] DB design help needed
[Solved] DB design help needed
Last edited by gleweliz on Wed Jan 02, 2008 12:59 am, edited 1 time in total.
Re: DB design help needed
Elizabeth, this is a standard database design issue, so it sounds like you need to trawl amazon or wherever for a beginners intro to database design. The answer to this specific Q is that add a third table ingredient_used_in_food which has at least three fields: ingredientCode, foodCode, quantity (and possibly quantityUnit) so it goes:
ingredient (1-many) ingredient_used_in_food (many-1) food.
Get the idea? With these the relationships and the query builder can build the queries that you want.
ingredient (1-many) ingredient_used_in_food (many-1) food.
Get the idea? With these the relationships and the query builder can build the queries that you want.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
Re: DB design help needed
Of course! Why didn't I think of that?
I'm terrible at reading instructions
Thanks very much!
Elizabeth
I'm terrible at reading instructions
Thanks very much!
Elizabeth
Re: SOLVED DB design help needed
TerryE
I have a similar issue. I got my three tables as you explained. But how do I get it to display in a Form with a subform.
Mine is addresses and Categories with a linkTable. I need to be able to link Categories to addresses.
But how do I get this to work in a Form? Any tutorials on doing that in Base?
Thanks for any input.
I have a similar issue. I got my three tables as you explained. But how do I get it to display in a Form with a subform.
Mine is addresses and Categories with a linkTable. I need to be able to link Categories to addresses.
But how do I get this to work in a Form? Any tutorials on doing that in Base?
Thanks for any input.
If the answer helped you solve the problem please set the subject o the thread to solved.
Re: [Solved] DB design help needed
Can you give us a few more details?
What fields are in each table?
How do the three tables relate to each other?
Whate tables and fields do you want to see on the form?
kabing
What fields are in each table?
How do the three tables relate to each other?
Whate tables and fields do you want to see on the form?
kabing
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)