UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Table Design, Access 2007    
 
   
eacollie
post Aug 9 2018, 11:43 AM
Post#1



Posts: 697
Joined: 15-January 05
From: Tennessee


I am keeping track of meals for events.
The event "host" allows for five different types of meals: diabetic, gluten-free, dairy-free, vegetarian and allergy.

I have the following tables:
Customer (customerID, etc)
CustomerSpecialMeal (customerID, specialmealID)
SpecialMeal(specialmealID, specialmeal)

My question is how to set up "allergy meals."

I ultimately have to report on the number of special means and then the details of each.
(So a person can be diabetic and dairy-free and also have an allergy to nuts, fish and avocados for example)

Hope this makes sense.
Go to the top of the page
 
tina t
post Aug 9 2018, 11:56 AM
Post#2



Posts: 5,480
Joined: 11-November 10
From: SoCal, USA


QUOTE
(So a person can be diabetic and dairy-free and also have an allergy to nuts, fish and avocados for example)

well, i don't know what your reporting requirements are, or how the database will actually be used, but it seems to me that the special needs describe the customer, not the meal. after all, a person who requires a diabetic meal and dairy-free meal will not get two meals, but one meal that meets all of his/her requirements - and s/he will not get the same meal as a person who requires diabetic and an allergy to eggs.

i have no specific suggestions re tables/relationships, just offer the above as another perspective on the data, for your consideration.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
eacollie
post Aug 9 2018, 12:10 PM
Post#3



Posts: 697
Joined: 15-January 05
From: Tennessee


Thank you Tina. That is the problem I am having, so not sure how to set up tables for this.

For reporting, I ultimately have to report a total number of "regular meals" and total number of "specialty meals" (and then be able to give the details of each of the specialty meals.)
Go to the top of the page
 
GroverParkGeorge
post Aug 9 2018, 01:11 PM
Post#4


UA Admin
Posts: 33,251
Joined: 20-June 02
From: Newcastle, WA


I agree with tina here.

You state it in the original statement of the question:

"... a person can be diabetic and dairy-free and also have an allergy to nuts, fish and avocados for example"

I would say that meeting that person's needs means picking items to include in a meal, and that the meal itself has no particular "identity". In other words, a meal is "dairy-free" by virtue of excluding dairy products, whereas an "allergy" meal is identifiable as such by virtue of excluding food products to which any given individual would be allergic. In fact, one person may be allergic to eggs, and not to fish, while another person is allergic to fish, and not to eggs.

What I think you need, therefore are tables that list all of the foods you include in any meal and a way to indicate whether that item is "diabetic" (whatever that means), whether it is "dairy free" and what allergies it may trigger. Then, when you need to construct a meal plan for a particular special need, you can limit the available items to those that meet the restrictions of that special need.

That leads to the following tables:

tblCustomer
===============
CustomerID
CustomerFirstName
CustomerLastName

tblCustomerRestriction
===============
CustomerRestrictionID
CustomerID
RestrictionID

tblMeal
===============
MealID
CustomerID
MealDate
MealTime (breakfast, lunch, dinner)
FoodItemID

tblFoodItems
===============
FoodItemID
FoodItemName

tblRestrictions
===============
RestrictionID
Restriction

tblFoodItemRestriction
==============
FoodItemRestrictionID
FoodItemID
RestrictionID

This structure allows you to indicate whether a food item, scrambled eggs for example, has restrictions such as Dairy or Allergy. Chocolate Cake, on the other hand, would have a restriction for Diabetic.

In this way, as you select a customer and identify any special menus that customer requires, you can limit the fooditems selected for their meals to those that qualify.

It also allows you to report easily on each meal served because the meal itself is constructed of foods.

I think there's more to consider, but that ought to get you moving.


--------------------
Go to the top of the page
 
Jeff B.
post Aug 9 2018, 01:22 PM
Post#5


UtterAccess VIP
Posts: 10,086
Joined: 30-April 10
From: Pacific NorthWet


I believe you stated that a mean could be 'regular' or 'special'. Those may be the only distinctions on meals.

As others have pointed out, restrictions/allergies/?preferences would apply to foods (if folks aren't being served stinging insects, which brings up a whole other category of allergies...<g>)

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
eacollie
post Aug 9 2018, 02:03 PM
Post#6



Posts: 697
Joined: 15-January 05
From: Tennessee


Thank you.
I'm going to let the people in the kitchen who prepare meals worry about how to fix them. I just need to create a report for them showing the basic "meal type" as mentioned above (five categories).
Go to the top of the page
 
projecttoday
post Aug 9 2018, 02:04 PM
Post#7


UtterAccess VIP
Posts: 9,980
Joined: 10-February 04
From: South Charleston, WV


Can a customer have more than 1 meal?

Can there be more than 2 types of meals? If so, each one is either regular or special, right?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
eacollie
post Aug 9 2018, 02:11 PM
Post#8



Posts: 697
Joined: 15-January 05
From: Tennessee


No, a customer will only have one meal at a time...breakfast, lunch and dinner are the three meals (but the customer's meal type shouldn't changed based on when the person is eating).

I'm going to report a meal as either "regular" or "special" and then break down the "special meal" for each person (hopefully...at least that's the plan!)
Go to the top of the page
 
projecttoday
post Aug 9 2018, 02:19 PM
Post#9


UtterAccess VIP
Posts: 9,980
Joined: 10-February 04
From: South Charleston, WV


How about this? 3 tables: customers, meal restrictions, customers-meal restrictions. Make a report of these 3 for the kitchen. If there is nothing in customers-meal restrictions for the customer the report says "regular".

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
eacollie
post Aug 9 2018, 02:23 PM
Post#10



Posts: 697
Joined: 15-January 05
From: Tennessee


Thank you. I've done this but just not sure about the "allergies." The other four (diabetic, gluten-free, dairy-free and vegetarian are pretty standard and don't change). Would I add all allergies into the table meal restrictions?
Go to the top of the page
 
tina t
post Aug 9 2018, 03:22 PM
Post#11



Posts: 5,480
Joined: 11-November 10
From: SoCal, USA


QUOTE
I'm going to let the people in the kitchen who prepare meals worry about how to fix them. I just need to create a report for them showing the basic "meal type" as mentioned above (five categories)
...
the customer's meal type shouldn't changed based on when the person is eating.

okay, i think i'm starting to get a picture of how the data will be used. based on your statement above, i think you don't really need a "meals" table at all. you just need to be able to issue a report listing x customers, with meal restrictions as "none" or "regular", or specific restrictions listed, for each customer.

based on the above, i'd probably start with the following tables, as

tblCustomers
CustID (pk)
FirstName
LastName
<other fields that describe a customer>

tblRestrictionTypes
TypeID (pk)
TypeName
<a list of the restrictions, one record per restriction: diabetic, gluten-free, dairy-free, vegetarian, allergy>

tblCustomerRestrictions
CRID (pk)
CustID (fk from tblCustomers)
TypeID (fk from tblRestrictionTypes)

tblCRAllergies
AllergyID (pk)
CRID (fk from tblCustomerRestrictions)
AllergyName (eggs, fish, peanut butter, etc.)

tblCustomers.CustID 1:n tblCustomerRestrictions.CustID
tblRestrictionTypes 1:n tblCustomerRestrictions.TypeID

the usual junction table to support a standard many-to-many relationship: one customer may have many restriction types AND one restriction type may apply to many customers.

tblCustomerRestrictions.CRID 1:n tblCRAllergies.CRID
one customer may have many allergies AND each allergy belongs to only one customer.

from a data entry standpoint, i would prevent a child record being added to tblCRAllergies unless there is a parent allergy record for the customer in tblCustomerRestrictions - conversely, i would require at least one child record in tblCRAllergies when a parent allergy record is entered in tblCustomerRestrictions. it will take some work in the data entry form to enforce the requirements, but it's certainly do-able.

from a reporting standpoint, i might just try a query with left join tblCustomers to tblCustomerRestrictions, and left join tblCustomerRestrictions to tblCRAllergies. in the report, you can hide repeating values so that you're left with a customer's name listed once, then each restriction type (if any) listed once, and finally each allergy (if any) listed once.

if the above doesn't work well, i might try a query with left join tblCustomers to tblCustomerRestrictions, bound to a report. then a subreport based on tblCRAllergies, to list those as needed.

btw, notice that i didn't include a tblAllergies. there are so many possible food allergies a person may have, that i didn't really see how it would be helpful. instead, i'd make field tblCRAllergies.AllergyName an open text field, so in the data entry form each person's specific allergies may be typed in, one per record. (it's tempting to type multiple allergies in as a long text string with commas, in one record - but i'd recommend against it. sooner or later somebody is going to ask for a breakdown of food allergies and it'll be easier if you have one record per allergy. there are issues there, as well, with an open field - misspellings, typos, etc - but there's a trick or two you can employ in a data entry form to help with that, though not eliminate it entirely.)

hth
tina
This post has been edited by tina t: Aug 9 2018, 03:29 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
projecttoday
post Aug 9 2018, 03:44 PM
Post#12


UtterAccess VIP
Posts: 9,980
Joined: 10-February 04
From: South Charleston, WV


I think allergies would be a subtable.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
eacollie
post Aug 9 2018, 03:47 PM
Post#13



Posts: 697
Joined: 15-January 05
From: Tennessee


Thanks Robert!
Go to the top of the page
 
eacollie
post Aug 9 2018, 03:49 PM
Post#14



Posts: 697
Joined: 15-January 05
From: Tennessee


Thank you Tina!
Go to the top of the page
 
projecttoday
post Aug 9 2018, 04:29 PM
Post#15


UtterAccess VIP
Posts: 9,980
Joined: 10-February 04
From: South Charleston, WV


thumbup.gif

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
eacollie
post Aug 9 2018, 07:48 PM
Post#16



Posts: 697
Joined: 15-January 05
From: Tennessee


Tina, why do you relate the tblCRAllergies to the primary key of the tblCustomerRestrictions and not to the customerID? A customer may have several records in the tblCustomerRestrions table.

Thank you
Go to the top of the page
 
tina t
post Aug 9 2018, 07:50 PM
Post#17



Posts: 5,480
Joined: 11-November 10
From: SoCal, USA


you're welcome, hon. hope it proves helpful, to give you some ideas if nothing else. :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
eacollie
post Aug 9 2018, 07:52 PM
Post#18



Posts: 697
Joined: 15-January 05
From: Tennessee


I think we posted at the same time.
Go to the top of the page
 
tina t
post Aug 9 2018, 08:10 PM
Post#19



Posts: 5,480
Joined: 11-November 10
From: SoCal, USA


lol, yes we did. but to address your question:

QUOTE
Tina, why do you relate the tblCRAllergies to the primary key of the tblCustomerRestrictions and not to the customerID? A customer may have several records in the tblCustomerRestrions table.

because one of the restriction types (see tblRestrictionTypes) is "allergy", per the list you cited in your first post. so the obvious business rule is that if you assign restriction type "allergy" to a customer (in tblCustomerRestrictions), then you must also state the specific allergy(s) the customer has - otherwise the restriction record is worthless as far as providing information to the real-world users of your report.

so following the above logic, tblCRAllergies is a child table that holds the details of each "allergy" type assigned to a customer, in tblCustomerRestrictions. think of it this way if it helps:

1. tblCustomers is the parent table and each customer may have many restriction types.
2. tblCustomerRestrictions is a child table of customers, holding the detail records that cite each restriction a customer may have.
3. tblCRAllergies is a grandchild table, holding the detail records for each "allergy" record in its' parent tblCustomerRestrictions.

so specific allergy records are firmly, though indirectly, linked to specific customers. setting your tables up in this straight parent>>child>>grandchild relationship will make much easier to support the business rule, and easier to create the query and report.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
eacollie
post Aug 9 2018, 08:22 PM
Post#20



Posts: 697
Joined: 15-January 05
From: Tennessee


Got it! Thanks again.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    18th August 2018 - 02:41 PM