Hi all,

I wonder if someone could reply to this - I kinda need help urgently. All suggestions will be warmly received.

I want to store information in a table and am not quite sure how best to do this. Basically, I want to store meal information. A user can select food items from a Food_Item table to make up meals which will be eaten on a particular day. So a person might select five food items to make up Breakfast, two for a snack, three for dinner and so on up to a maximum of six meals. Obviously this means storing alot of data (for six meals). I should mention that the Food_Item table has a primary key called Food_ID (a unique number for each food in the database).

I also need to store the date/time the meals are scheduled for. How can I do this? Each meal (up to six allowed) would have to store several Food_ID entries but that would be a crazy amount of fields in one row and I'm not sure how to go about this. Please help,

Thanks,

Synergy

This strikes me as a fairly straightforward "parent/child" relationship, very much like the relationship of an invoice to the line-items being ordered.

I'd have a table of meals_scheduled which describes each meal that is scheduled to be served, with reference to the person to whom it is being served, when and where, and so on. Relating to this is another table called, say, dishes_ordered. Each record therein points to the meal it belongs to, and also contains a sequence-number (1, 2, 3, 4, etc...) giving its position on the list. It also contains the index of the food being served.

Thanks for the reply - but I'm still not sure.

I can go with your suggestion for two tables. One table will store the User_ID along with dates/time for meal and the number of meals scheduled for that day. With the Dishes_Ordered table I'd need to relate to the Schedule_Table - how? Would this be through the User_ID? I'd also have to link the dates in these two tables? So would the primary key for the Schedule_Table be both User_ID & Date? If so, I set up the Dishes_Ordered table - can I put multiple fields/columns for the same attribute? i.e. A person might have 6 meals prepared for a day (these would have a row each?) and each meal consists of up to 10 Food_Items. I'd have to have 10 columns to reference each Food_Item? Each Food_Item is a seperate entry in the database and has it's own unique number (multiple Food_Items numbers would have to be stored in each row). So a table for a user, for one day of meals, might have 6 rows in the table with up to 10 food_Items in each row, along with a foreign key (in this case the User_ID & Date_Of_Schedule?). Does this sound ok? Doing this I'll be repeating the User_ID & Date_Of_ Schedule in the Dishes_Ordered table (up to six times depending on how many meals a user has prepared for that day.

Thanks for your help so far - it's been helpful and was very swift. Hopefully you can reply again, as can anyone else reading this who feel they can contribute - cheers,

Synergy

"Connect the dots ..." the computer certainly can.

As a general rule of thumb, plan on having a database table for:

• every "thing" in your world (users, food, meals, people, dishes), describing only the properties of that "thing" and any "one-to-one" or "one-to-many" relationships it may have;
• every many-to-many relationship if you have any;
• and, perhaps, every event, such as a scheduled meal.

Use this rule-of-thumb as a starting point for your design, and if you find that certain tables are not needed you can eliminate them. There is no "right" answer.

Now, as for how those tables can be linked, which seems to be the crux of your present question.

Tables do not have to be linked directly to each other because in an SQL query I can join any reasonable number of tables, following any "chains" of relationships that I have going in my database. For example, let's figure out how many potatoes (and while we're at it, every other food item) that I am going to need for all the meals that are to be served today. To do this, the query must follow this chain of table relationships: (These relationships are not "specified in advance," but merely occur in the query I'm about to write.)

• In [b]meals,[b] (which in my current concept of the database serves as the schedule) locate all the meals with date_scheduled = today().
• With an inner join to dishes, find all of the food items that are present in any of those meals.

A simple query like this would list the food items, eliminating duplicates; not quite what we want although useful. Let's do more...

We use the count(ingredient) clause to specify that we want to know the count of the ingredients (obviously...) and we then use GROUP BY ingredient to specify that we want a unique row for each ingredient and the count of occurrences for each group.

Summary:

• The database schema describes the world as the computer knows it.
• The querying capability of the SQL database provides the answers sought, figuring out how to produce each answer at the moment it is presented.

As the book of Psalms says, "selah ..." ("ponder this")