Database schema design help needed

Question

I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance.

This application will have 1M users, each user will have their own dashboard where they can create their own users/roles, pages and posts.

I am thinking about two possible solutions

1. Use only 1 table for all users, pages and posts and they will have a foreign key of the owner
2. Use separate tables for all users, like user1_pages, user1_posts, user1_users

I Think with #1 the query will be slow as all users will be sharing one table, second one also don't seems a perfect solution as number of tables will increase exponentially.

Please suggest me a best possible solution and share tips to design an optimized MySQL schema.

Answer 1

You should use one table per object of interest. That means one table for users, one table for pages, one table for posts, etc. Use a normalized database (See database normalization) for transactional data. This is precisely what relational database management systems are built to do. Don't presume you are going to have performance problems because you have many rows. Most systems perform better with many rows than with just a few.