where do i store label info in a contacts database for mailings

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:

• mr & mrs joe thomson
• dr. and mrs james berry
• Schwartz family

This seems like it would have to be a calculated field based on a number of d开发者_如何学编程ifferent 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:

• SALUTATION (Mr, Mrs, Dr, etc)

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:

• salutation

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

• the user is not forced to scroll through a long list of possible values
• you don't need to administrate the values yourself

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.