在数据库中为不同的用户类型存储多个电子邮件地址 [英] Store multiple email addresses in database for different user types

问题描述

关于最高效的快速问题是处理这个问题，我有一个数据库具有用户类型的树结构，

Quick question about the most efficient was to handle this, I have a database which has a tree structure of user types,

system ---  1 to m --- account --- 1 to m --- customer  

所有三种类型可以有多个电子邮件地址，我想知道什么是最好的方式来处理这个在数据库中，所有用户类型都有auto_id作为主键，所以有一个表的地址标识相关主键不会工作将有潜在的每个3。例如电子邮件与键2，将链接到系统与ID 2，帐户ID 2等。

All three types can have multiple email addresses, I was wondering what would be the best way to handle this in a database, all user types have auto_id as their primary key so having a table with address identified by there related primary key wont work as there will be potentially 3 of each. e.g. email with key 2 , will link to system with id 2 , account with id 2 , etc.

我应该为每个类型创建一个电子邮件地址的表。还是有更优雅的解决方案。

Should I create a table of email addresses for each type .? Or is there a more elegant solution .?

感谢

推荐答案

外键，它是system_id，account_id或customer_id。然后您可以有一个字段指定该外键的类型。另一个更复杂的策略是仍然有电子邮件表，但没有外键。另一个表，你可以调用email_relation，由email_id和外键组成。

You either have an email table that has a foreign key which is either a system_id, account_id, or customer_id. Then you can have a field specifying the type of that foreign key. Another more complicated strategy would be to have still the email table but no foreign key. Another table which you would call email_relation consisting of the email_id and the foreign key. That way you could use one e-mail address for all three tables.

使用两个表的示例

system
--------
s1 
s2
s3

account
--------
a1
a2
a3

customer
--------
c1
c2
c3

email
------
e1 [e-mail]
e2 [e-mail]
e3 [e-mail]
e4 [e-mail]

email_relation
---------------
email_id     foreign_id      relation_type
e1           s1              system
e1           a1              account
e1           c1              customer
e2           c1              customer
e3           c2              customer
e4           a3              account
e4           c3              customer

如果您希望客户表包括电子邮件地址

if you want the customer table including the e-mail address

select c.customer_id, e.email
from customer c
left join email_relation r on (r.foreign_id = c.customer_id and relation_type = 'customer')
left join email          e on (e.email_id    = r.email_id)
where r.email_id is not null

如果您希望所有电子邮件到系统，您也可以

If you want all e-mail to a system you could also

select e.email
  from email e
  join email_relation r on (r.email_id = e.email_id and relation_type = "system")
 where r.foreign_id = 1 

这篇关于在数据库中为不同的用户类型存储多个电子邮件地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！