>I'm trying to develop a web-based order management system which will allow hundreds of subscribers (hundreds of different companies) to use the same web system to manage their orders and customer data.
>I guess this kind of application is known as an "Application Service Provider".
>
>Each subscriber would enter many orders and many customers into the system. And the system would assist them in managing this information.
>
>Here's my question: Would it be better to have one "customer" table and one "orders" table... and one of every table needed for the app, and to therefore allow all the subscribers data to be in the same master data tables... and each subscriber would be able to see only their own data... pulled out with SQL commands.
>OR... should I consider a separate set of tables for each and every subscriber?
>
>I hope I made this clear enough to understand. An example: If I put all the subscribers data into one set of tables, I might only need 10 or 12 tables. (customers,orders,products,invoices,etc.) But each of the tables would contain all the data for all subscribers. The alternative would be to create separate directories for each subscriber where the "private" data tables would reside separately for each subscriber.
>
>I plan to use SQL Server as a data back-end. If I get thousands of subscribers, it seems that maybe separate directories is the way to go, perhaps set up as subdirectories under a directory for each state in the USA for example.
>
>Any thoughts? Thanks
>Joel Aiken
>joel@patsyaiken.com

Keeping data for all subscibers in common tables will make life a lot easier for reporting. For example, what if you want to know the total value of all orders for all subscribers for a given month? Much easier if the data for all subscribers are in the same tables.