I have developed an application that users will be able to download and create an account with through our company. This application will always be connected to the internet. I have been testing the application with a local SQL database up to this point, but our goal is to have the database hosted online and I need to start thinking big picture. I have come up with a couple solutions, I would just like to here some input/advice from someone who is more experienced in the database world.
Here is the problem:
Our users sell products and we are an order manager. The tables that are going to have lots of entries will be Orders, Shipments, Archived Orders, Archived Shipments, etc. (especially for large companies that have thousands of daily orders.) I am trying to decide the best way to organize the tables/database to accommodate the growth our company will receive once the product is released (a couple hundred customers, a couple thousand in a couple years, not unlikely.) Just off the top of my head, I have a couple ideas:
1) One large Orders table, one large Shipments table. These tables store the thousands of orders/shipments that are added daily, they can be queried based off some company index. These tables will obviously get large (hundreds of customers adding thousands of orders/shipments daily)
2) Individual Orders and Shipments tables for each customer. So the name of the table would essentially be the organizer for different companies orders/shipments. Is this a logical answer for a situation like this?
3) Some sort of system where say the first X customers orders/shipments are added to one database's tables, and then when we get more than X customers, another database/server would be created to divvy up the server power/storage.
Obviously you can tell I am not an expert and some of my questions may seem vague, but all I am looking for is some advice/possible solutions to move forward and start integrating my application with databases/servers on the cloud with the mindset that many customers will be added in a small period of time and I will need to approprately divide/add resources/servers/tables. Any input is appreciated.