I'm designing a messaging system that will have to support a huge amount of messages and users.
我正在设计一个消息系统,它必须支持大量的消息和用户。
I was thinking about two solutions.
我在考虑两种解决方案。
Usertable -> id, username ....
Messagetable -> id, from_id, to_id, message ...
Or:
Usertable -> id, username ....
Messagetable -> id, message ...
HasMessagetable -> id, from_id, to_id...
I'm wondering what is the best approach to this and why.
我想知道什么是最好的方法和原因。
Also, are there good publications (free or not) about large databases design and best practices?
此外,是否有关于大型数据库设计和最佳实践的好的出版物(免费或不免费)?
Thank you
3 个解决方案
#1
1
I did the same not too long ago and started out with approach 1. But then users were supposed to be able to send messages to multiple users. Suddenly approach 1 saved each message n times if n recipients were addressed. So if this is ever a possibility, I think 2 is better.
不久前我做了同样的事情,并从方法1开始。但是后来用户应该能够向多个用户发送消息。如果n个收件人被解决,突然接近1保存每条消息n次。所以,如果这是一种可能性,我认为2更好。
#2
1
Your second schema is more normalized. Both are acceptable. Properly normalized database design is cleaner, but for perfomance reasons many DBA resort to denormalisation. I would use the second schema, until you hit performance issues this would be the better approach in my very humble opinion.
您的第二个架构更加规范化。两者都可以接受。适当规范化的数据库设计更清晰,但出于性能原因,许多DBA采用非规范化。我会使用第二个模式,直到你遇到性能问题,这将是我非常谦虚的观点中更好的方法。
Do note that normalising to that extent is often considered overkill by many, as others have posted. I do it that way from habit and old (now outdated) DB theory courses I learned 12 years ago.
请注意,正如其他人发布的那样,正常化到这种程度通常被许多人视为过度杀伤。我是从12年前学到的习惯和旧的(现在过时的)DB理论课程那样做的。
Happy-coding
#3
1
In general, the less joins you have to do, the better your queries will perform. Therefore, the first option will probably be a better choice since you are going to have a very large database.
通常,您必须执行的连接越少,查询执行的效果就越好。因此,第一个选项可能是更好的选择,因为您将拥有一个非常大的数据库。
Basically, you are going to need to ignore some database normalization techniques in order to gain the performance you need. However, try not to limit yourself either. For example, if you have messages that go to multiple people, you are going to need to either choose option two or figure out a different way to handle this.
基本上,您将需要忽略一些数据库规范化技术,以获得所需的性能。但是,尽量不要限制自己。例如,如果您有多个人的消息,您将需要选择选项二或找出一种不同的方式来处理这个问题。
As for resources for large database design, here is one for Microsoft SQL Server but a lot of the things it discusses will apply:
至于大型数据库设计的资源,这里有一个用于Microsoft SQL Server,但它讨论的很多东西都适用:
#1
1
I did the same not too long ago and started out with approach 1. But then users were supposed to be able to send messages to multiple users. Suddenly approach 1 saved each message n times if n recipients were addressed. So if this is ever a possibility, I think 2 is better.
不久前我做了同样的事情,并从方法1开始。但是后来用户应该能够向多个用户发送消息。如果n个收件人被解决,突然接近1保存每条消息n次。所以,如果这是一种可能性,我认为2更好。
#2
1
Your second schema is more normalized. Both are acceptable. Properly normalized database design is cleaner, but for perfomance reasons many DBA resort to denormalisation. I would use the second schema, until you hit performance issues this would be the better approach in my very humble opinion.
您的第二个架构更加规范化。两者都可以接受。适当规范化的数据库设计更清晰,但出于性能原因,许多DBA采用非规范化。我会使用第二个模式,直到你遇到性能问题,这将是我非常谦虚的观点中更好的方法。
Do note that normalising to that extent is often considered overkill by many, as others have posted. I do it that way from habit and old (now outdated) DB theory courses I learned 12 years ago.
请注意,正如其他人发布的那样,正常化到这种程度通常被许多人视为过度杀伤。我是从12年前学到的习惯和旧的(现在过时的)DB理论课程那样做的。
Happy-coding
#3
1
In general, the less joins you have to do, the better your queries will perform. Therefore, the first option will probably be a better choice since you are going to have a very large database.
通常,您必须执行的连接越少,查询执行的效果就越好。因此,第一个选项可能是更好的选择,因为您将拥有一个非常大的数据库。
Basically, you are going to need to ignore some database normalization techniques in order to gain the performance you need. However, try not to limit yourself either. For example, if you have messages that go to multiple people, you are going to need to either choose option two or figure out a different way to handle this.
基本上,您将需要忽略一些数据库规范化技术,以获得所需的性能。但是,尽量不要限制自己。例如,如果您有多个人的消息,您将需要选择选项二或找出一种不同的方式来处理这个问题。
As for resources for large database design, here is one for Microsoft SQL Server but a lot of the things it discusses will apply:
至于大型数据库设计的资源,这里有一个用于Microsoft SQL Server,但它讨论的很多东西都适用: