Table Design Decision Question (1 Viewer)

Hi All,

I'm using my upcoming wedding as an opportunity to learn about using Access to build databases.

1. I've a table of RSVP'd wedding guests and their e-mails
2. A table of the children names, ages and a lookup field (linking kids to parents in the above RSVP guest table)

The source of my confusion is how to track the dietary requirements. I've had guests filling out free text field forms on the internet that tell me if they
- need milk for the baby
- no pork
- wheat intolerance
- fish (but not meat) etc...

Que: Should I create a new table (maybe called "dietNeeds") to represent this data then link this also to the adults &/kids OR should I make this free text data part of the already created tables?

Thanks for your willingness to help.

I would actually have one table for guests (one record per guest) with a field to indicate who is the 'party contact' and another couple of fields, one to indicate who is that party's contact (like your kids table lookup) and perhaps one to indicate the relationship (e.g. parent, spouse, etc). This will help with your table planning. The fact you are only interested in the ages of children just means this field would be left blank for adults. Similarly the email address only needs to be completed for party contacts and left blank for everyone else.

From my experience it will be easier if you can phrase all dietary requirements consistently as positive or negative - for example 'vegetarian', 'vegan', 'gluten free' would be a positive (because it is saying what they can have), 'no pork' is a negative (because it is saying what they can't have). But this will depend on how you are planning to feed your guests and what you are going to be telling the caterers. As to whether to incorporate this in the guest table or a separate table, It breaks normalisation rules but I would suggest simply incorporate into the guest table - in this scenario every guest is going to be eating the same number of courses so basically you have a one to one relationship anyway.

However if you are going to be detailing what everyone is having course by course I would put this in a separate table with fields of guestid, courseid, menuitem

CJ_London said:

As to whether to incorporate this in the guest table or a separate table, It breaks normalisation rules but I would suggest simply incorporate into the guest table

Click to expand...

Putting the dietary requirement attribute in the guest table doesn't (as far as I can see) break normalisation rules. If guest determines dietary requirement then under any Normal Form the dietary requirement attribute belongs in the guest table.

Putting the dietary requirement attribute in the guest table doesn't (as far as I can see) break normalisation rules

Click to expand...

yes you are right - providing it is only one 'condition'. So 'vegetarian and no mushrooms' would break the rule

CJ_London said:

I would actually have one table for guests (one record per guest) with a field to indicate who is the 'party contact' and another couple of fields, one to indicate who is that party's contact (like your kids table lookup) and perhaps one to indicate the relationship (e.g. parent, spouse, etc). This will help with your table planning....

From my experience it will be easier if you can phrase all dietary requirements consistently as positive or negative...

Click to expand...

Thanks CJ_London

So If I understand your suggestion correctly I would create one flat "guest table" with dietary requirements attached to each guest as an additional record (no separate "child table")?

p.s It's a buffet style dinner so I really need to have an understanding of the number of guests that are going to require fish or halal meat or allergic to nuts etc... still not sure if each option and allergy should be an individual field in a seperate food table or if it's better practice to have a combo box allowing multiple selections in one field attached to each guest record as part of the 1 and only "guest table"?

I would create one flat "guest table" with dietary requirements attached to each guest as an additional record (no separate "child table")?

Click to expand...

No, I was suggesting it would be a field in the guest table - but if you were recording what each guest was having for each course, that would be a separate table - but you are doing a buffet so suggest that is not needed.

With the additional info you have provided, you may want two columns - one for 'positives' or requirements (fish, vegetarian, halal, etc) and one for negatives or allergies/intolerence (nuts, gluten) etc.

So you can simply count the number of portions to know how many you need of each type of dish required

SELECT requirement, count(requirement) as NumberRequired
FROM Guests
GROUP BY requirement

Click to expand...

and to find the number of dishes or portions required to cater for the allergies

SELECT allergies, count(allergies) as NumberRequired
FROM Guests
GROUP BY allergies

Click to expand...

Okay that is not actually good data base design -- I have worked with Access but also MySQL, SQL Server, and Oracle and the basics of database design are the same regardless. A database should be designed with the data in mind not its use or application that is what Views and such are for.


I think that covered everything you mentioned if you have questions on the why's I will be more than happy to answer them.

don't disagree with you in principle but think your solution goes further than required.

for example tblrelations has a one to one relationship with tblguests, so do not see the benefit of keeping it separate

tblRelationships - happy to debate the value of having single value lookup tables in this scenario - debate on which is more efficient - having a FK in a field to look up a single text value or just store that text value instead - where do you stop? (second cousin, friend, spouse of friend etc).

tblChildren - this is slightly less efficient in terms of storage utilisation than maintaining it within the guest table, (which you acknowledge) but accept it is considered a more correct approach

tblSpecialDiet - does not meet the needs for someone who is for example 'vegetarian' and 'allergic to nuts' - because you have a one to one relationship with the guest table, however has made me realise the although someone won't be 'vegetarian' and 'halal', they could be 'allergic to nuts' and 'gluten intolerant'. So I accept this is a better solution with the amendment that it has it's own PK and Guest_ID becomes a FK

No tblRelationships does not have a 1 to 1 relationship with tblGuests for instance

John is Married to Sally and they have 3 kids and he has a brother and sister that are coming as well.

John Mary Wife
John Kid Son
John Kid Daughter
John Kid Daughter
John Bro Brother
John Sis Sister

That is a 1 to Many if I ever saw one

tblChildren is also known as a 1 to 1/0 relationship meaning that not all the records in the primary table will have values in the child table which keeps the primary table free of numerous null values -- provided the child table is made up of more than one piece of data. I created it just in case they might want to add more information about children.

Again tblSpecialDiet is a 1 to Many/0 which is to say it can be 1 to 0 or 1 to 1 or more
So in tblSpecialDiet we get

200 1
200 2

I hope that clarifies what might not have been easily seen.

No tblRelationships does not have a 1 to 1 relationship with tblGuests for instance

Click to expand...

tblRelations (this table will get very big so we want to keep it very small)
Guest_Id (PK + FK)

Click to expand...

if guest_id is a PK - how can you have more than one john? I was thinking the other way - which is a one to one relationship

Mary John Wife
Kid1 John Son
Kid2 John Daughter
Kid3 John Daughter
Bro John Brother
Sis John Sister

same for

tblSpecialDiet
Guest_Id (PK + FK)

Click to expand...

unless I'm misunderstanding what you mean by (PK + FK) - I assumed it fulfilled both functions of a PK for tblSpecialDiet and a FK or tblGuests and would be a long datatype rather than autonumber. PK's by definition need to be unique for each record - ah just noticed the pk assignment to dietary_id so take it back for tblSpecialDiets.

I guess if you made RelatedTo in tblRelationships a PK as well this would overcome my point above, however if you viewed the data the other way round as above, you would have a 1 to 1 relationship - unless you needed a record to show kid1 to be related to kid2 as sibling

Ah okay I fixed that mistake in tblRelations forgot the 2nd PK

PK = Primary Key

PK + FK = Primary Key that is also a Foreign Key
tblSpecialDiet is correct as shown as PK stands for Primary Key but when a table has more than one PK that means that all the PKs together make up the Primary Key for a record