I am trying to setup a contacts database for mailings and I am trying to fully automate the labels but can't figure out how.
Where in the database would I store the name that would appear on the top of a mailing label:
This seems like it would have to be a calculated field based on a number of different pieces of data.
Any suggestions on how you have a mailings database and generate names for labels directly?
Building off the data model I suggested in your previous question, I'll update the CONTACTS table to include:
I would determine to use "Thompson Family" versus Mr. Joe Thompson and Mrs. Terry Thompson and Joe and Billy based on the number of personal contacts = 2+ for the same address, with the same last name.
References:
A fairly normalized design would look something like:
Location(addr_id,primary_contact_id, street_addr, city, post_code, country)
Contact(contact_id,first_name, last_name, title);
LivesAt(contact_id,addr_id)
MarriedTo(contact_id_1,contact_id_2)
ChildOf(parent_id,child_id)
These would basically be your tables. Then you could create views:
1.Family. Assume a family is at least one parent and a child living at the same address, and they share the same surname (in the case of a parent and child having different surnames, you will address the letter to them both by their full names).
CREATE VIEW Family AS
SELECT UNIQUE last_name, addr_id, street_addr, city, post_code, country FROM
(SELECT p1.contact_id, p1.first_name, p1.last_name FROM Contact AS p1)
INNER JOIN
(SELECT p2.contact_id, p2.first_name, p2.last_name FROM Contact AS p2)
ON (p2.last_name = p1.last_name AND p2.contact_id IN ChildOf
AND p1.contact_id IN ChildOf)
INNER JOIN
Location AS l
ON (p1.contact_id = l.primary_contact_id)
OR (p2.contact_id = l.primary_contact_id)
Format as you see fit.
2.Married couples with no children.
CREATE VIEW Couple AS
SELECT * FROM
(SELECT C.contact_id, C.last_name, C.title FROM Contact AS C
INNER JOIN MarriedTo AS M
ON (M.contact_id_1=C.contact_id)
INNER JOIN
SELECT D.contact_id, D.last_name, D.title FROM Contact as D
ON (M.contact_id_2=D.contact_id)
INNER JOIN Location AS L
ON
L.addr_id NOT IN Family
AND (L.primary_contact_id = M.contact_id_1)
OR (L.primary_contact_id = M.contact_id_2)
And so on.
I personally don't like encoding these kind valid values in the database column definition. You'd have a large administration overhead. Managing these values in a distinct table is better, but having a foreign key into another table you to read the label just didn't seem right to me. Last time I needed to do something similar I added the column for the label as a simple varchar column.
But what do you do to avoid duplicates and very similar labels (e.g. "Mr" and "Mr.")? I added an index on the column and added an AJAX query in the frontend to list all distinct labels available and perfom an autocompletion. This works really awesome because
This way you'd allow any label including "Mr. and Mrs." or "Prof. Dr. Dr.".
Given your clarifications - I would create an additional table.
I'm assuming you have a "contacts" table which contains a distinct list of people. You could also have a "household" table which contains a list of last names or households. I would put the address in THIS table. Then, the each contact person would have a field linking them to a household (even if there is only one person per household.) Each contact person would also have a field for "primary contact" containing a 1/0 value.
Then, you could have query logic something like the following:
if count(*) of contacts per household = 1 then
label = contact.title & contact.nameinfo
if count(*) of contact per household = 2 and both of those contacts are primary contacts then
label = primarycontact.title & name plus primarycontact2.title & name
else
label = household.lastname & "family"
You'll want to play with the logic to get it perfect, but the real key is having a household table with a separate address and a contact table with the people within that address.
