动态联系人信息数据/设计模式：这是否可行？ [英] Dynamic contact information data/design pattern: Is this in any way feasible?

问题描述

我目前正在研究一个具有许多联系信息的实体（人员，组织）的网络业务应用程序，即。多个邮政地址，电子邮件地址，电话号码等。

目前，数据库模式使得person表具有邮政地址列，电话号码列以及组织表。这不是一个很好的方法来处理这个问题。

我已经阅读了c2 Wiki，并且有一些关于联系人和地址模型（ http://c2.com/cgi-bin/wiki?ContactAndAddressModels ），并且不要物理地址是古老的（ http://c2.com/cgi-斌/维基？ArePhysicalPostalAddressesArchaic ）。这两个讨论真的让我看到了这个问题的范围。

我正在考虑分开联系信息字段来分隔表。但是最好的方法是什么？目前，该应用程序主要处理芬兰地址，但是它也在处理国际地址。

我可以定义一个地址表，一个电话号码表，一个电子邮件地址表等，这些将与人联系起来和组织。但是，这只是感觉像以前的解决方案一样：预定义的数据库模式是不可避免的。

我建议的是创建一个联系信息模式/ 动态的程序逻辑：

• 没有预定义的联系信息字段/字段集


• 用户可以随时定义新的联系信息类型和必填字段，例如
  
  
  
  • 芬兰邮政地址
  
  
  • 瑞典语邮寄地址
  
  
  • ...邮政地址
  
  
  • 电话号码
  
  
  • 电子邮件地址
  
  
  • ICQ-number
  
  
  
  

这是否可行？有没有人做过这样的事情？

可能有一个表定义联系人信息类型：

联系信息类型

• ID：标识符


• 名称：芬兰邮政地址


• 说明： 使用此联系信息类型进行芬兰邮政地址

然后可能有一个表定义每个联系人信息类型使用哪些字段：

联系信息类型字段

• ID：标识符


• Contact_information_type_id：参考上一张表


• 字段标题：地址行1

<字段描述：将此行用于邮政地址第一行

• 字段类型：String / Integer / etc。


• 字段格式：正则表达式用于验证字段数据


• 字段顺序：显示/使用此联系人信息类型时应显示此字段的顺序







然后，我们将有一个联系信息表，用于将联系人信息字段映射到一起：





联系信息






• Id：标识符


• Contact_information_type_id ：参考联系信息类型表








然后我们会有一个联系人人的信息 - 表映射到人员的不同联系信息：





个人的联系信息






• Id：标识符


• Contact_information_id：引用联系信息表


• 个人ID：引用该人








然后，我们需要每个联系人信息字段类型的表，如：





联系信息整数字段




• Id：标识符


• Contact_information_id：引用联系信息表


• 价值：val ue这个字段








等字符串等...





最后，当显示给定人物的不同联系信息时，这将通过个人的联系信息来查找 >联系信息类型字段 -table通过联系信息 -table。在确定使用哪些字段后，所有必需的表将被连接在一起。





我对SQL的可行性有疑问。任何想法？





在Java中，我可能会编程一些逻辑来确定需要哪些表来形成联系人信息实体，然后我可以使用某种动态bean来表示这个数据在Java中。但这对我来说也是有点雾。

解决方案

开始听起来像你有一个完美的锤子（即你的SQL数据库）和你正试图用它创建另一个锤子（一种用于定义SQL模式的元语言）。





在你走下去之前，市场上有很多产品旨在将客户详细信息存储在SQL数据库中。最好只是购买一个现成的并与之集成。那么所有你所关心的问题都是由别人解决的，你可以专注于你的具体业务案例。





编辑：一个允许你添加自定义联系人的包的例子字段是 SugarCRM - 它是一种商业产品，您可以在其中购买访问权限购买的来源。我相信还有更多，但这是目前唯一想到的一个。




I'm currently working on a web business application that has many entities (people,organizations) with lots of contact information ie. multiple postal addresses, email addresses, phone numbers etc.

At the moment the database schema is such that persons table has postal address columns, phone number columns as does organizations table. This is not a good way to handle this.

I've read the c2 Wiki on this and there's some good discussion regarding Contact and address models (http://c2.com/cgi-bin/wiki?ContactAndAddressModels) and wheter or not physical addresses are archaic (http://c2.com/cgi-bin/wiki?ArePhysicalPostalAddressesArchaic). These two discussions really opened my eyes on the scope of this problem.

I'm thinking about separating contact information fields to separate table(s). But what's the best way to do this. At the moment the application mainly handles Finnish addresses but it's on the horizon that it needs also to handle international addresses.

I could define an "addresses" -table, a "phone numbers" -table, an "email addresses" -table and so on and these would be linked to people and organizations. But this just feels too much like the previous solution: it's inevitable that the predefined database schema isn't sufficient.

What I'm proposing is to create a contact information schema/program logic that is dynamic:

• There are no predefined contact information fields/field sets
• Users can define new contact information types and required fields at any time like
  • Finnish postal address
  • Swedish postal address
  • ... postal address
  • Phone number
  • Email address
  • ICQ-number

Is this feasible? Has anyone done anything like this?

There could be a table that defines contact information types:

contact information types

• Id: Identifier
• Name: "Finnish postal address"
• Description: "Use this contact information type for finnish postal addresses"


Then there could be a table that defines what fields are used per contact information type:

contact information type fields

• Id: Identifier
• Contact_information_type_id: References the previous table
• Field title: "Address line 1"
• Field description: "Use this line for postal addresses' first line"
• Field type: String/Integer/etc.
• Field format: Regular expression for validating field data
• Field order: In which order should this field appear when displaying/using this contact information type


Then we'd have a "contact information table" that just is used to map contact information fields together:

contact information

• Id: Identifier
• Contact_information_type_id: References the contact information type table


Then we'd have a "contact information of person" -table mapping different contact information to persons:

contact information of person

• Id: Identifier
• Contact_information_id: References the contact information table
• Person id: References the person


Then we'd need tables per contact information field type like:

contact information integer fields

• Id: Identifier
• Contact_information_id: References the contact information table
• Value: The value of this field



and so on for strings etc...

Finally when displaying different contact information of a given person this would happen through person's contact information -table whis looks up what fields are used to form this contact information from contact information type fields -table through contact information -table. After determining what fields are used all the necessary tables would be joined together.

I'm having doubts about the feasibility of in SQL. Any thoughts?

In Java I probably could program some logic to determine what tables are neede to form a contact information entity and then i could use some sort of dynamic beans to represent this data in Java. But that's a bit foggy to me too. Anyt thoughts on this too?

解决方案

It is starting to sound like you have a perfectly good hammer (i.e your SQL database) and you are trying to make another hammer with it (a meta-language to define SQL schemas).

Before you go down this path, there are many products on the market that aim to store customer details in an SQL database. It might be best to just purchase one off the shelf and integrate with it. Then all the concerns you have are addressed by someone else and you can focus on your specific business case.

Edit: One example of a package that allows you to add custom contact fields is SugarCRM - it is a commercial product where you buy access to the source on purchase. I'm sure there are many more but this is the only one that comes to mind at present.

这篇关于动态联系人信息数据/设计模式：这是否可行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！