I'm creating a Web based billing and inventory management system. I need to store the product details such as name, price, stock left and also want to store details of every bill for each user uniquely. Should I create separate table for every user to store these information?
Creating a unique table for every user to hold their billing/purchase details would be an absolute nightmare to maintain.
It would be best to create different tables for each entity. For example, you could create one for all users, one for all bills/purchases, and one for all products. You can connect the user table and billing table by a user ID. In the billing table, each row can have a user ID to signify which user that purchase record belongs to. The user ID would be a reference back to the user table. The billing table should also have a reference to a products table which would hold the product name, stock left, etc.
This is just an example though and depending on your system design, you may need more tables.
Creating new tables for each user is a bad design pattern and causes more work in the long run. One of the issues that can quickly become apparent is trying to retrieve data from the system.
If you need to return a report on multiple users, for example users who have not paid their bill you would now have to search through all of the tables in order to find users who have not paid. This could cause you to have to look through a lot of tables where you will not find a match in order to get to the data you need.
However if you have all the data in a single table you are able to use the where clause and a single select statement to get the data you need which will be much more maintainable in the long run.
As for how many tables you will need that will depend on what data is being stored and you will need to take time to design that.
No you don't need separate tables for every user to store information. Just create Product table with its detail such as Name Price Qty
When you sale products to customer/user your product qty should be triggered for -(Stock out) When you purchase stock your product qty should be maintained as +(Stock In)