I'm developing an application and need to propose a database schema.
We plan for a MySQL database with millions of records which should be distributed over multiple machines for performance and availability reasons.
The schema contains an element Category and we will have from 10 to 100 different categories. The number of categories will vary during the system's lifetime. Each query will be relevant for a single category only, and must be ACID-compliant. This means several queries for different categories can (and should!) run in parallel.
I see the following options:
- Define a single table and partition it horizontally.
- Use separate tables for each category.
Since I don't have experience in administering database servers and we only have a small amount of categories, I'm wondering which of the two options is easier to administer and what are their other advantages and disadvantages.