Creating Relationships - Required?

[Leo]

Hello,

I am attempting to create a database using Access 2010. I am going to require three tables; Patients, Foods and Meals tables to create a Nutritional assessment program.

My Foods table is going to be a database of food items. I will need to be able to add & remove items from this table independent from any relationship from any of the other tables. The plan is for the Nutritionist to use the Meals & Patients table to create an individual meal plan for a given patient. The information that make up the meal planning will be from items taken from the Foods table.

My questions;

When should I create a relationship between the related tables? I notice that once I made the connection (relationship) - I cannot modify the table(s). I cannot go back and add fields to the table. I also attempted to use the Lookup Wizard (it did not work) in one of the existing fields and could not.

Also,

My Foods table is just going to be a large database - do I have to create a relationship with the other tables? I want to be able to add & remove data entries from this table, possibly add another field if required.

 

[jdraw]

Do NOT use lookups at the Table field level.

Your tables and relationships will be based on some facts

A Meal may consist of 1 or Many Foods
A Food may exist in 0,1 or many Meals
There are 1 to Many Patients
A Patient has 1 to Many Meals (I'm trying to visualize possible meals for 1 patient)
A Patient gets a prescribed/specific Meal
eg. Low sodium chicken soup suitable for diabetic

Now for preparation and delivery there would by 3 meals per day

But this is a general layout (I don't know all details of your situation)


Patient ----->PatientMeal<-----Meal<-----Food

 

[Leo]

Yes, that is the basics.

My issue is that the Food Items table is going to be something that we continue to update by two methods:

• The Nutritionist will be able to manually include any item not found within the table.
• Data will be imported / added to the table to provide items not currently listed.

Therefore, I think my table relationship should look like:

Patient ----->Meal<-----Food

True - A Meal may consist of 1 or Many Foods
True - A Food may exist in 0,1 or many Meals
I don't understand? - There are 1 to Many Patients (Do you mean 1 patient to many patients?)

True - A Patient has 1 to Many Meals (I'm trying to visualize possible meals for 1 patient)
True - A Patient gets a prescribed/specific Meal - (However, there can later be updates / revised meal plans)
eg. Low sodium chicken soup suitable for diabetic

I am starting very basic but I can already see that I am going to encounter addendums to the meal plan(s) and currently establishing relationships stops me from later revising any of the related tables.

 

[Galaxiom]

I can already see that I am going to encounter addendums to the meal plan(s) and currently establishing relationships stops me from later revising any of the related tables.

If your tables need to be modified to handle changes in meal plans then the data is not correctly structured.

Structure must not be used to store information. Records store all information in the values they hold.

Do not use a field for each food in the Meals table.

Foods to Meals will require a junction table. One field will be MealID, the other FoodID. Another field will hold the quantity of each FoodID in the Meal.

There will be many records for each meal.

 

[Leo]

Do not use a field for each food in the Meals table.

Sorry, I do not think that I explained my problem well:

I have a table name Foods. This table will hold records that will consist of endless food possibilities. But my understanding is that the food entries (the actual records) do not have the be associated with a Patient or a Meal (fields in my other tables). The objective is; when a meal is created, the Nutritionist can select from an infinite possibility of food items (records) within the Food table. Meaning the Meal table is referencing the Food table for records.


However, I notice that once the relationship is established between the tables - I cannot add fields. I am thinking ahead, but what if I am asked to start keeping track of something that I am not currently accounting for in my existing table(s)? I seem to only be able to accomplish that by creating another table?

 

[Galaxiom]

A field can't be deleted or modified if it is in a relationship without deleting the relationship first. However relationships should not prevent changing, adding or deleting other fields in either table.

Food to Meals is a Many-To-Many Relationship so it will be through a junction table. There shouldn't be a direct relationship.

 

[RainLover]

Try not deleting any record.

Place an extra field in the tables (Yes/No) would do. Mark the record as No instead of deleting it.

 

[smig]

another thing to think of:
Do you need to know the exact historical Nutritional data of each meal ?

An example:
The amount of sodium in a specific food may change. Do you need to look at the meal's history and know how much sodium was in any specific meal ?
If so, you you should copy the current Sodium data from the food table into the meal's table.
Another option will be never changing any details in the food table. Once there was a change you create a new food with the new details and make the old one as inactive.

 

[Leo]

The amount of sodium in a specific food may change. Do you need to look at the meal's history and know how much sodium was in any specific meal?

I am only at the design phase, but yes - something I did not think about until you mentioned it. Each patient will come in for a consultation. The meal plan is customized for their needs. I am going to need to maintain a history, the referring physician can at a later time request the information that was provided to the patient. Therefore, a copy of the information needs to be retrievable without it having any modifications or updates because a food item has changed.

Any idea how I would accomplish that - I am currently reading up on relationships…so I don't have all the answers at this time.

 

[RainLover]

If a Meal stays the same, except for the Sodium then place a Change Date against that meal. You will need another table to record this as it may happen many time.

Then for the recall of history you call pull up those meals based upon the date.

Note. You only need the Date Started or Date Changed. Same thing. No need for Date Start and Date End on the same record/meal.

 

[Leo]

I believe I have my tables and their relationships established.

Tables.....Patients........Meals......................Foods
Keys.......Patient_ID.....Meal_ID..................Food_ID
.................................Patient_ID_FK..........Meal_ID_FK

In this relationship, I should be able to accomplish my need to create records in the Foods table as new items are introduced. I should also accomplish the need to retain historical data on each patient, the relationships are:

Patients can have many meals - which have unique times (Breakfast, Lunch…etc) and that is divided more by the consult date.

Each meal is categorized by the time and consult date. This allows the collection of historical data.

I appreciate your comments...

 

[RainLover]

Where is your Primary Key?

Display your tables North to South not East to West

Plenty of examples around for you to look at.

 

[Leo]

Sorry,

I created a graphic to hopefully better explain the direction I am going with the table(s) layout:

Same criteria:

In this relationship, I should be able to accomplish my need to create records in the Foods table as new items are introduced. I should also accomplish the need to retain historical data on each patient, the relationships are:

Patients can have many meals - which have unique times (Breakfast, Lunch…etc) and that is divided more by the consult date.

Each meal is categorized by the time and consult date. This allows the collection of historical data.

 

[RainLover]

Foods

Try calling this tblFoods. I will attach a paper on naming conventions.

The Table tblPatients should have a primary key of PatientPK which should be of Data type Autonumber.

There are many meals of the exact same design. So it has a Primary key of PKMeals and should not be connected to Patients. Instead place another Table in the middle that has The Meal and the Patient. The patient may have several meals of the same design.

You could also add another Table joined to this new one to record Breakfast Lunch, Date etc.

Finally Foods and meals are joined but you have them back to front.

I would suggest that you don't use that drawing program. Instead use a New Blank database and create the tables and joins in there.

If you then Post that data base we could go looking to improve.

 

[gemma-the-husky]

I think your structure is not right.

you have a meals table and a foods table. So this depends on whether your food table is "complete servings" or "ingredients" , and what you mean by "meal"

if a meal is "starter", "main", and "dessert", then you need a more complicated structure anyway. If you have a kitchen that is trying to make "porridge" from "milk" and "oats" then it is even harder still.

I think your structure needs a lot of attention.

 

[Leo]

Thanks for the input…

Let me take a step back and show what I am attempting to accomplished. I have attempted to normalize the needed fields from this example taken from the currently used document. This document exist in a Word format and we are attempting to create the process within Access.

The color coded areas indicate the fields I require - I did not highlight all the food items. However, I hope from the illustration it is apparent that I need this part of the report to be able to include multiple selections (not the same items each time but individualize to the patient).

 

[RainLover]

Thanks for the input…

Let me take a step back and show what I am attempting to accomplished. I have attempted to normalize the needed fields from this example taken from the currently used document. This document exist in a Word format and we are attempting to create the process within Access.

The color coded areas indicate the fields I require - I did not highlight all the food items. However, I hope from the illustration it is apparent that I need this part of the report to be able to include multiple selections (not the same items each time but individualize to the patient).

Leo,

Sorry but what are we looking at. Is it a Report, a Form or a Table.

If this was done in Access then pleas post the Database so that we can get an even better understanding. Please limit to Access 2007

 

[Leo]

Rain,

I had to go back and study up on junction tables. At this point, my understanding is that I can accomplish what I need with two junction tables. I have a many-to-many scenario with my tblFoods & tblMeals and also with tblMeals & tblPatients.

I uploaded what I have to receive your input.

 

[RainLover]

Leo

Sorry but I have been otherwise occupied. I will have a go now.

 

[RainLover]

First is a tidy up of the tables.

First NO SPACES. This is in both the Table name and the field names.

Underscore is acceptable but most do not like its use.

I will look for more problems/solutions.