Need help designing new database

[djl0525]

I'm pretty good in Access, but I'm starting a new database, a food diary, and I'm a little stuck on how to design the tables. It's a seven day diary, seven consecutive days based on food groups such as fruits, veggies, etc. The client completes a paper survey sheet per day. They fill in their name and address and the first date of their diary. In each food group, they tell how much they ate on that day of an item (apple, grape, broccolli, milk, etc.). They pick from 3 serving sizes. And for each item they tell us whether the item is organic or conventional.

I know I need a food group table and a food item table. And I have my client table. Where I'm stuck is how to create the date table and which table to relate it to.

Thank you in advance for your consideration,
DJ

 

[djl0525]

I know my database has ME stumped, but I didn't think it would stump all of you. Is there anyone out there who can help me get started? Pleeeeease...

DJ

 

[Andrew Fergus]

Hi

I think you have a many-to-many relationship between the people, the dates and the items of food. In other words one person can have many meals made up of many items of food, and one food item can be consumed by many people on many days (sounds terrible I know, but I'm trying to describe how a many-to-many relationship works).

Have you looked at this webpage? Without physically seeing what you are trying to do makes this difficult, but I suggest you have a think about the layout of the paper version of the 'food diary' and which pieces of information you want to capture in the data (note I said data, not date) table. For example, I would imagine this table would include a person id (linked to the person table), possibly a meal id (linked to a meal type table), a date, a food type id (linked to the food items table), a portion size, and an organic indicator (to name just the minimum).

Don't worry about the forms and how they will look until later - as you may well know, having the incorrect data structures can make Access very difficult to handle.

I trust this points you in the right direction
Andrew

 

[djl0525]

Thank you Andrew. Your response is indeed helpful. I overlooked the M:M relationship between clients and the food. I will create a junction table between these two.

The data table was a complete surprise. I was going to put the portion size and the organic indicator in the food items table because I thought they were about the food. But I see now why that is wrong. It would mean that every tomato eaten by every person were all the same size and always organic (or conventional). So now I'll put the indicator and portion size in the data table. But I gotta admit, I haven't gotten my head around the Data table yet. I have two questions about it.

1. What might be the primary key of the data table?

2. Is there a M:M between the data table and the client table and the data table and the food table?

If yes, that means my 3 main tables all have M:M between them. I've seen this in sample databases before, but it never applied to any of my databases. Can you elaborate a little on the Data table, please?

One more question. I was going to have a Food Categories table (the food table I refered in this post) and a related Food Items table. Does this sound right to you?

Thanks again for taking the time to help me with my design. You've been a big help already!

DJ

 

[Andrew Fergus]

You are correct with your thoughts about organic, portion size etc. - these would need to be recorded in the data table. And yes the food category (e.g. fruit) would be linked 1:M to the food items table (e.g. apple).

The data table would have exactly the fields I described in my previous post - namely, person id (linked M:1 to the person table), possibly a meal id (linked M:1 to a meal type table - the meal type table might inclue breakfast, lunch & dinner etc.), a date, a food type id (linked M:1 to the food items table), a portion size (e.g. small, ,med, large etc.), and an organic indicator (e.g. yes/no). The M:M relationship is achieved by using two 1:M relationships through a junction table - in your case this junction table is the 'data' table with the fields I described.

To answer your specific questions:

1. What might be the primary key of the data table?
The primary key of the data table could be one of 2 things. Either it is an autonumber field which is meaningless other than giving you a unique id. Or it could be a combination of fields (like all 4 fields combined of the person id, the date, the meal id and the food item id).

2. Is there a M:M between the data table and the client table and the data table and the food table?
No there is a M:1 between the data table and the client table and another M:1 between the data table and the food table.

3.I was going to have a Food Categories table (the food table I refered in this post) and a related Food Items table. Does this sound right to you?
Yes - see above.

HTH, Andrew