I was wondering which approach is better when making a database (ie for a messaging app). Lets say that there is a table with all the messages. is it better to either:
- Have one large table for all the messages or
- Have one table for the most recently sent messages and another for all messages sent over, for example, 1 month ago.
My thinking with this is that it is better to go with the second option, as it will be faster to search through a smaller database for the queries that are going to be the most common instead of going through a huge table with all the messages ever sent. I’ve tried to look up best practices for large databases but I have not found anything that answers this question.
Advertisement
Answer
One table for all the messages. There is simply no good reason to split a single entity into multiple tables, under most circumstances (there are some specialized circumstances where it might be a good idea). Relational databases are designed to work with large tables, not with large numbers of tables.
If performance is an issue, learn about indexing and table partitions.
Here are some reasons why you don’t want multiple tables:
- Maintaining tables (adding columns, defragging, adding an index) over time is a real pain.
- Constructing queries to look at all the data is a real pain.
- Partially filled data pages can use up a lot of disk and memory.
- Backup and restore is painful, compared to the alternative of using partitions.
- Security and permissions are painful.
There are few reasons for having multiple tables in one database. One reason may be to meet security requirements. Often, applications are built using their own databases with copies of the tables. This is part of the application design.