我在哪里存储邮件联系人数据库中的标签信息

发布于 2024-08-07 15:45:21 字数 224 浏览 7 评论 0原文

我正在尝试为邮件设置联系人数据库,并且正在尝试完全自动化标签,但不知道如何实现。

我将在数据库中的哪个位置存储将出现在邮寄标签顶部的名称:

  • mr &乔·汤姆森
  • 女士博士和詹姆斯·贝里·
  • 施瓦茨夫人家族

这似乎必须是基于许多不同数据的计算字段。

关于如何拥有邮件数据库并直接生成标签名称有什么建议吗?

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 different pieces of data.

Any suggestions on how you have a mailings database and generate names for labels directly?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

哆兒滾 2024-08-14 15:45:21

构建我在上一个问题中建议的数据模型,我将更新 CONTACTS 表以包括:

  • 称呼(先生、夫人、博士等)

我将决定使用“Thompson Family”来代替 Joe Thompson 先生和 Terry Thompson 夫人以及 Joe 和 Billy基于个人联系人数量 = 2+(同一地址、同一姓氏)。

参考文献:

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:

揽清风入怀 2024-08-14 15:45:21

一个相当标准化的设计看起来像这样:

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)

这些基本上就是你的桌子。然后你可以创建视图:

1.家庭。假设一个家庭至少有一名父母和一名孩子居住在同一地址,并且他们具有相同的姓氏(如果父母和孩子的姓氏​​不同,您将用他们的全名来写这封信)。

    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)

根据您的需要设置格式。

2.无子女的已婚夫妇。

    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)

等等。

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.

寻找一个思念的角度 2024-08-14 15:45:21

我个人不喜欢在数据库列定义中对这些有效值进行编码。您将承担大量的管理开销。在不同的表中管理这些值更好,但在另一个表中使用外键来读取标签对我来说似乎并不正确。上次我需要做类似的事情时,我将标签列添加为简单的 varchar 列。

但是您如何避免重复和非常相似的标签(例如“先生”和“先生”)?我在列上添加了一个索引,并在前端添加了一个 AJAX 查询来列出所有可用的不同标签并执行自动完成。这真的很棒,因为

  • 用户不必被迫滚动一长串可能的值,
  • 您不需要自己管理这些值,

这样您就可以允许任何标签,包括“先生和夫人”。或“教授、博士、博士”。

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.".

心在旅行 2024-08-14 15:45:21

鉴于您的澄清 - 我将创建一个附加表。

我假设您有一个“联系人”表,其中包含不同的人员列表。
您还可以有一个“家庭”表,其中包含姓氏或家庭列表。我会将地址放入此表中。
然后,每个联系人都会有一个将他们与家庭联系起来的字段(即使每个家庭只有一个人)。
每个联系人还将有一个包含 1/0 值的“主要联系人”字段。

然后,您可以具有如下所示的查询逻辑:

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"

您需要使用该逻辑以使其完美,但真正的关键是拥有一个具有单独地址的家庭表和一个包含该地址内的人员的联系人表。

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文