Linking Tables with Main Form

[Guest]

Hi,

I am creating a recipe database. I have my tables created (course, cuisine,
ingredients, main ingredient). Course, cuisine and main ingredient are a
list and ingredients will be built as they are entered into the form.

That being said, I have made relationships of the four tables to the table
recipes, which will hold everything and integrity is not enforced on any.

I am wondering at this point how I have my form (meaning the design of it)
link to my tables (tables with list box) and regular text box (i.e.
description of recipe is a text box and there is a column in tblRecipes).

Is my relationship setup correctly?

Thank you very much for your imput.

 

[Guest]

Hi sm,

Hi,

I am creating a recipe database. I have my tables created (course, cuisine,
ingredients, main ingredient). Course, cuisine and main ingredient are a
list and ingredients will be built as they are entered into the form.

That being said, I have made relationships of the four tables to the table
recipes, which will hold everything and integrity is not enforced on any.

Enforce Referential Integrity should always be applied unless you have a
*VERY* good reason not too. Ideally cascade update and delete should also be
ticked, but this is not such a hard and fast rule in a database, their are
many who ignore cascade delete, but I wouldn't recommend it especially if the
database is going to grow to any size.

I am wondering at this point how I have my form (meaning the design of it)
link to my tables (tables with list box) and regular text box (i.e.
description of recipe is a text box and there is a column in tblRecipes).

The best way is to create Queries (often 1 per form) to draw together the
data you need for each form. Without integrity and cascade update, you may
have great difficulty getting the data you want and not ending up with orphan
records to confuse everything.

Is my relationship setup correctly?

Can you post the acual table design, ie include field names and datatype for
each table. The basic premise of your design of tables looks sound (separate
data into separate tables), but without the fields it's hard to say.

Thank you very much for your imput.

TonyT..

 

[Guest]

Here is the design of my DB:


Relationships:
**I get an error "no unique index found for the referenced field of the
primary table".

All of these four are simply connected from their table to tblRecipes :

tblMainIngredient, tblIngredients, tblCourse, tblCuisine

MainIn(MainingredientID, main ingredient)
(auto#, text - requ-no, allow zero-no, index-dupes ok, LIST BOX tbl/qry
bound 1, column1, heads no)

Ingredients(IngredientID,Ingredients)
(auto#, text, text box)

Course(CourseID, Course)
(auto#, text, requ-yes, allow-zero, dupes ok, listbox , tblqry

Cusine(CuisineID,Cuisine)
(auto#, text, requ-no, allow-no, dupes ok, LIST BOX, tblqry)

tblRecipes(RecipeID, Recipename, recipedescription, source, course, cuisine,
main ingredient, ingredients, vegetarian (check box), timetoprepare, #of
servings, instructions, notes)

IN ORDER OF LISTING from tblRecipes:

auto#
text, requi-no, allow no, dupes ok, TEXT BOX
text, requi-no, allow no, allow zero no, indexed no, TEXT BOX
text, requi-no, allow no, indexed no, TEXT BOX
text, requi-no, allow no, dupes ok, TEXT BOX
text, requi-no, allow no, dupes ok, TEXT BOX
text, requi-no, allow no, dupes ok, TEXT BOX
Memo, requi-no, dupes ok,
YES/NO, requi-no, check BOX
Text, requi-no, allow no, indexed no, TEXT BOX
Number, requi-no, TEXT BOX
memo, requ-no, allow no, indexed no,
memo, requi-no, allow no, indexed no


Thank you for your help!

 

[Guest]

Hi again,

replies in order below;

Here is the design of my DB:


Relationships:
**I get an error "no unique index found for the referenced field of the
primary table".

The *Child* field in the tblRecipes for each of the other (parent) tables
needs to be a number field, and you need to make sure that the joins are
between the correct tale fields, eg. your reltionship line between MainIn
table and tbl Recipes should go between MainIngredientID<-->Main Ingredient
with MainIngredient on the left handside and Main Ingredient on the
right(related table).

All of these four are simply connected from their table to tblRecipes :

tblMainIngredient, tblIngredients, tblCourse, tblCuisine

I would suggest that having a separate table for MainIngredient is going to
complicated matters *alot* a the design progresses. You would be better off
creating another (copy) instance of tblIngredients and linking that again too
tblRecipes as a one-to-one join (or one-to-many if it is deemed that their
can be more than on Main ingredient in any one recipe). This way it will be
far simpler to search for a recipe based on an ingredient, lets say avacado.
If a user searches for recipes with the ingredient avacado as a non-main
ingredient your setup will miss any recipes that have avacado as the Main
Ingredient unless you code for 2 searches.

MainIn(MainingredientID, main ingredient)
(auto#, text - requ-no, allow zero-no, index-dupes ok, LIST BOX tbl/qry
bound 1, column1, heads no)

I very, very, very strongly recommend that you do not use Lookups in your
tables designs, it creates strong limitations to where you can go in the
future with your design. Leave it up to queries to *join* your separate table
data, and Forms to display it for you as you progress with the database
design. Nothing but a severe headache waiting to happen I'm afraid.

Ingredients(IngredientID,Ingredients)
(auto#, text, text box)

Course(CourseID, Course)
(auto#, text, requ-yes, allow-zero, dupes ok, listbox , tblqry

Cusine(CuisineID,Cuisine)
(auto#, text, requ-no, allow-no, dupes ok, LIST BOX, tblqry)

tblRecipes(RecipeID, Recipename, recipedescription, source, course, cuisine,
main ingredient, ingredients, vegetarian (check box), timetoprepare, #of
servings, instructions, notes)

If it's not too late in your design process, I think one more table would be
a helpfull addition, and that would be too separate out RecipeName,
description time to prepare etc etc (recipe descriptive information that
really only needs to be stored once) into another table let's say
tblRecipeDescription and leave the main tblRecipe looking like;
tblRecipe
CuisineID | Long Integer (PLEASE no Lookups )
CourseID | Long Integer
RecipeDescriptionID | Long Integer <- linked one-to-many to new table above
IngredientID | Long Integer

With this set-up, your tables may not be very helpfull when looked at in
table view, as you won't know what ingredient belongs to what recipe, but
will be so much clearer (and faster) to bring the information together in
Forms and Reports when you get into designing your Queries.

IN ORDER OF LISTING from tblRecipes:

auto#
text, requi-no, allow no, dupes ok, TEXT BOX
text, requi-no, allow no, allow zero no, indexed no, TEXT BOX
text, requi-no, allow no, indexed no, TEXT BOX
text, requi-no, allow no, dupes ok, TEXT BOX
text, requi-no, allow no, dupes ok, TEXT BOX
text, requi-no, allow no, dupes ok, TEXT BOX
Memo, requi-no, dupes ok,
YES/NO, requi-no, check BOX
Text, requi-no, allow no, indexed no, TEXT BOX
Number, requi-no, TEXT BOX
memo, requ-no, allow no, indexed no,
memo, requi-no, allow no, indexed no


Thank you for your help!

sorry to throw up obstacles, but as you have probably gathered by now, the
holy grail of database design is normalisation, just google it for more
*yawn* extensive reading!

good luck and post back with your progress,

TonyT..