在此之前
表“用户”：
id | mail | service (comma separated)
-----------------------------------------------
1 | [email protected] | TV, Internet
2 | [email protected] | Cloud, Internet
3 | [email protected] | 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 | [email protected]
2 | [email protected]
3 | [email protected]
table_messages
message_id | title | text
-----------------------------------------------
1 | Disruption | ...
2 | Maintenace | All services offline
3 | Disruption | Some services offline
table_services
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
最佳答案
您的架构看起来不错，并遵循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)
接收所有相关信息。
关于mysql - 如何规范这个MySQL表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54237915/