[Solved] DB design help needed

Creating tables and queries
Post Reply
gleweliz
Posts: 11
Joined: Wed Nov 28, 2007 3:41 pm
Location: Michigan, USA

[Solved] DB design help needed

Post by gleweliz »

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
Last edited by gleweliz on Wed Jan 02, 2008 12:59 am, edited 1 time in total.
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: DB design help needed

Post by TerryE »

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.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
gleweliz
Posts: 11
Joined: Wed Nov 28, 2007 3:41 pm
Location: Michigan, USA

Re: DB design help needed

Post by gleweliz »

Of course! Why didn't I think of that?
I'm terrible at reading instructions :oops:
Thanks very much!
Elizabeth
Fundi
Posts: 22
Joined: Mon Jan 28, 2008 1:44 am

Re: SOLVED DB design help needed

Post by Fundi »

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.
If the answer helped you solve the problem please set the subject o the thread to solved. :-)
User avatar
kabing
Volunteer
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: [Solved] DB design help needed

Post by kabing »

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
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)
Post Reply