Quick note:
This is the first database I'm setting up, no one else is doing it and I thought hey I'm a smart guy I can do this (btw everything looks easier on youtube). I greatly appreciate the community's help that I have been receiving.
Project:
I'm setting up a database for a food pantry, where a client is defined as a person and there should only be one person picking up for a household. For example if Bob registers and comes the first week week and Sue (Bob's wife) comes the next, Sue should be counted as picking up for Bob.
So I set up a query that pulls the building number, apartment number and zip code from the address. A database user will manually go through the list and evaluate whether or not the address is unique. The user could check if 123 Seventh Ave, Apt 9J, 12345 (123, 9J, 12345) is the same as 123 7th Ave, Apt 9J, 12345 (123, 9J, 12345). Or the user will verify 123 Seventh Ave, Apt 9J, 12345 is the not the same 123 6th Ave, Apt 9J, 12345. (Side note since I just forgot: city and state info is defined by zip codes)
Question:
How would you handle if two individual people, from the same address, pick up on and off?
The database user is guaranteed to see an address when a client registers but not when the client re-visits. And not every client has his or her pantry ID card on hand.
Today I'm not worried about database size but it may potentially become rather large, the location is a metropolitan city. (hopefully they will have a professional redesign the database before then)
Currently
My first thought was to let the registration go through and the database user would notice from a query that an address repeated, and would delete the address. There is a column for additional family members (this field was suppose to take names and B-days to record evidence for a large family, they get a little more food). It's not set up to list just names.
Idea 1)
Allow the registration of a secondary to go through and have a table to link accounts. Using a primary account to track visits. So if a client is looked up and they are seen as a secondary (Sue), it would display the ID# of the primary to search for (Sue's husband Bob) and then the user would file the transaction under the primary (Bob).
So I'll call the new table Shared Accts:
Make the Secondary Accts the Primary Key (1st column), and primary acct 2nd column. So incase three people register under the same address.
Idea 2)
Step 1) Register a Client's Address as a Client 2.0
Step 2) Register a Client 1.0 by Last Name, First Name, B-Day, mailing Address. Then attach the Client 1.0 to a Client 2.0 (I keep the mailing address in case matching between Client 1.0 and Client 2.0 gets accidentally changed)
Step 3) To record a transaction: (1) Look Up Client 1.0's corresponding Client 2.0 (2) Record transactions by Client 2.0
My thoughts
I feel like I should go with Idea 2 but I'm uneasy about Idea 2 because the verification of a unique household is done manually and the structure doesn't seem intuitive. I think Idea 2 will cut down on the questionnaire storage since the information pertains to the people in the household (Bob, Sue, Grandpa Bob, and Bob Jr.) rather than the person picking up (Bob).
TL
R How would you handle if two individual people, from the same address, pick up on and off?
After an hour of editing this post :banghead: Thanks in advance
This is the first database I'm setting up, no one else is doing it and I thought hey I'm a smart guy I can do this (btw everything looks easier on youtube). I greatly appreciate the community's help that I have been receiving.
Project:
I'm setting up a database for a food pantry, where a client is defined as a person and there should only be one person picking up for a household. For example if Bob registers and comes the first week week and Sue (Bob's wife) comes the next, Sue should be counted as picking up for Bob.
So I set up a query that pulls the building number, apartment number and zip code from the address. A database user will manually go through the list and evaluate whether or not the address is unique. The user could check if 123 Seventh Ave, Apt 9J, 12345 (123, 9J, 12345) is the same as 123 7th Ave, Apt 9J, 12345 (123, 9J, 12345). Or the user will verify 123 Seventh Ave, Apt 9J, 12345 is the not the same 123 6th Ave, Apt 9J, 12345. (Side note since I just forgot: city and state info is defined by zip codes)
Question:
How would you handle if two individual people, from the same address, pick up on and off?
The database user is guaranteed to see an address when a client registers but not when the client re-visits. And not every client has his or her pantry ID card on hand.
Today I'm not worried about database size but it may potentially become rather large, the location is a metropolitan city. (hopefully they will have a professional redesign the database before then)
Currently
My first thought was to let the registration go through and the database user would notice from a query that an address repeated, and would delete the address. There is a column for additional family members (this field was suppose to take names and B-days to record evidence for a large family, they get a little more food). It's not set up to list just names.
Idea 1)
Allow the registration of a secondary to go through and have a table to link accounts. Using a primary account to track visits. So if a client is looked up and they are seen as a secondary (Sue), it would display the ID# of the primary to search for (Sue's husband Bob) and then the user would file the transaction under the primary (Bob).
So I'll call the new table Shared Accts:
Make the Secondary Accts the Primary Key (1st column), and primary acct 2nd column. So incase three people register under the same address.
Idea 2)
Step 1) Register a Client's Address as a Client 2.0
Step 2) Register a Client 1.0 by Last Name, First Name, B-Day, mailing Address. Then attach the Client 1.0 to a Client 2.0 (I keep the mailing address in case matching between Client 1.0 and Client 2.0 gets accidentally changed)
Step 3) To record a transaction: (1) Look Up Client 1.0's corresponding Client 2.0 (2) Record transactions by Client 2.0
My thoughts
I feel like I should go with Idea 2 but I'm uneasy about Idea 2 because the verification of a unique household is done manually and the structure doesn't seem intuitive. I think Idea 2 will cut down on the questionnaire storage since the information pertains to the people in the household (Bob, Sue, Grandpa Bob, and Bob Jr.) rather than the person picking up (Bob).
TL
R How would you handle if two individual people, from the same address, pick up on and off? After an hour of editing this post :banghead: Thanks in advance
Last edited:
