0

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?

  • 9
    No you shouldn't. This is the bad design pattern that violates the relational principles and vastly complicates the further proceeding. – Kondybas Feb 1 at 3:20
0

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.

0

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.

  • I would disagree with this statement that three tables is what should be done as it could be more tables are needed and that won't be known until you get down to designing the details of the system. For example you may want two tables for bills/purchases one with information about the bill/purchase itself and another with all the items associated with the bill/purchase. – Joe W Feb 1 at 14:39
  • Sure, that is definitely possible. It's impossible to tell without more information about the system that Shibi is setting up though. I just wanted to give them a high-level way of designing the system to start off with and steer them away from doing unique tables for each user purchase. – Eric MPC Feb 1 at 14:58
  • Which is why I said suggesting 3 tables isn't best. While it is clear that creating a new table for each user isn't a good idea it also isn't clear how many tables would be best for what is being attempted here. – Joe W Feb 1 at 15:01
  • The three tables were merely a simple example of a possible design. I edited the answer to make it more clear that it is an example and not the absolute only design path that Shibi could take. – Eric MPC Feb 1 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.