What do you think is the best approach for a PHP and SQL based web application that will be used by a number of people?
For example, say we have a table called "sales" and a user wants to access his sales. The table should contain a foreign key of the user_id or it will be better to make a separate table for each user?
Any other implementations and opinions are also welcome!
In my opinion best approach would be using two tables and refer from a foreign key. Make sure to use indexes as well. MySQL has done various optimizations to WHERE clause on a PRIMARY KEY or a UNIQUE index[1]. So you will be fine when working with considerable number of records(ex: handling 100000 records won't be a issue if you have capable hardware for database instance and optimized database configurations accordingly).
Make sure to do database optimizations based on your system to increase performance as well. Better to do in-house testing to make sure system is upto your expectations in long run.
[1] http://dev.mysql.com/doc/refman/5.7/en/where-optimizations.html
By sales I take it you mean the users invoices or billing history. You would want to separate them out into two tables using foreign keys. You could have a users table and an invoices table with the userid as the foreign key in your invoices table.
Whenever the user wanted to view their invoices, you would select rows from the invoices table where the userid is that users id.
