mysql - 如何规范化这个 MySQL 表

Question

之前

表“订阅者”：

id | mail            | service (comma separated)
-----------------------------------------------
1  | [e-mail] | TV, Internet 
2  | [e-mail] | Cloud, Internet 
3  | [e-mail] | Phone, Cloud

表“消息”包含：

id | title      | message               | service (comma separated)
------------------------------------------------------
1  | Disruption | TV services offline   | TV
2  | Maintenace | All services offline  | TV, Internet, Cloud 
3  | Disruption | Some services offline | TV, Internet 

这是正常化的正确方法吗？

table_subscriber

sub_id | mail
-----------------------------------------------
1      | [e-mail] 
2      | [e-mail] 
3      | [e-mail]

表消息

message_id | title   | text
-----------------------------------------------
1          | Disruption | ...
2          | Maintenace | All services offline
3          | Disruption | Some services offline 

表服务

service_id | title
-----------------------------------------------
1          | TV
2          | Internet
3          | Phone

table_subscriber_and_services

订阅者可以订阅多个服务

sub_id | service_id
---------------------
1       | 1
1       | 2

table_messages_and_services

消息可以引用多个服务

message_id | service_id
---------------------
1          | 1
1          | 2

标签： mysql

Answer 1

您的架构看起来不错，并且遵循 1NF、2NF 和 3NF。
现在您需要表格来收集所有信息，例如：journal：

id | sub_id | service_id | message_id
--------------------------------------
1  |      1 |          1 |          2
2  |      1 |          2 |          3

现在您可以运行如下查询：

SELECT
  subscriber.mail, service.title, message.title, message.text
FROM journal j
JOIN table_subscriber subscriber USING (sub_id)
JOIN table_services service USING (service_id)
JOIN table_messages message USING (message_id)

接收所有相关信息。