SQL and the Soup Kitchen

At a soup kitchen on Potrero St in San Francisco is a man named Charlie. Charlie has been working to take care of the homeless for over thirty years. One of the responsibilities he’s taken on is having the kitchen’s address available for those in need to use to receive their mail. Looking through the mail that’s accumulated takes Charlie, or whoever is checking the mail for someone, can take forever.

Searching for someone's mail consists of looking one by one for the recipient's name on each envelope. At worst finding, or not finding, a single piece of mail can take upwards of fifteen minutes.

A simple SQL solution could make life incredibly easier. When mail arrives, why not populate a simple table in SQL consisting of a recipient & arrival date?

For example:

CREATE TABLE mail (
id INTEGER PRIMARY KEY,
recipient_id INTEGER,
arrival_date TEXT
delivered BOOLEAN);

CREATE TABLE recipient (
id INTEGER PRIMARY KEY, 
name TEXT);

Presto! From here we have a starting point to make simple queries to the database which would identify if someone has a letter waiting.

No longer will soup burn when you have to look for mail!

Comments

[zakwillis]

Hi Adam, I think you will find the answer is - Pesto (Just had Pesto Chicken for dinner), rather than Presto? :)

Here goes a code review...

CREATE TABLE mail (
id INTEGER PRIMARY KEY,
Recipient_id INTEGER, /you could/
arrival_date TEXT
delivered BOOLEAN);

CREATE TABLE recipient (
id INTEGER PRIMARY KEY,
name TEXT);

Instead...
I wrote this outside an SQL editor, so forgive me if I get this wrong. Assuming you are using SQLLite rather than something like SQL Server or MySQL? I am basing this off of SQL Server - which is free in certain versions and not to be sniffed at.

The key point is. It is vital to not simply rely upon Entity Framework when modelling data and process flow. Indeed, you may find that Event Sourcing is a far better approach - something a database and/or NoSQL database is adept at handling.

CREATE TABLE kitchen.Mail (
MailId INTEGER identity(1,1),
RecipientID INTEGER, /you could enforce a foreign key constrain here/
Arrival datetime constraint df_kitchen_Maild1 default getdate(),
AddressId int /Indicates we probably store the address separately./,
Delivered BOOLEAN /How would you know it had been delivered if you hadn't received it?/
,LetterDate date
, constraint pk_kitchen_Mail primary key (MailID) );

/The name recipient... this means the person who the address was sent to. Now, perhaps it should be called addressee?/
CREATE TABLE kitchen.Recipient (
RecipientId INTEGER identity(1,1) ,
RecipientCode nvarchar(20), /people can have the same name?/
Forename nvarchar(200)
Surname nvarchar(200)
,AddedOn datetime constraint df_kitchen_Recipient1 default (getdate())
,constraint pk_kitchen_Recipient primary key (RecipientId )
);

/There is a lot more to consider here than meets the eye./
Perhaps we need a gender, perhaps not?
Perhaps we need a sender address table?
Perhaps we need a sender table?
Perhaps we need a stored procedure to manage adding a first time letter versus a return hungry person? MERGE STATEMENT?
Perhaps we should use a sequence as opposed to an autonumber?
We use schemas to attach context to an entity.
Perhaps there are multiple delivery mechanisms - Post, DHL, Amazon?
What about forwarding addresses?
What happens if it is a letter from a bailiff or somebody even more untoward - public health?
What about GDPR?

How about creating some functions to really add some metrics to this?