I am building a webapp business management system. The backend that supports the app is currently under design and I am wondering on the following:
In reality, each business' records have nothing to do with another business' records. I would like to design my db to reflect this for a number of logical and practical reasons. This means that one business' records are not located in the same table (or DB or instance, depending on design and DBMS) as anothers. How do I design to accommodate this? (I have chosen to use Sql Server 2005 at present)
It would seem practical to store the app's data in a single-instance, single-database system, since neither instances nor databases can be created in large enough quantities without tremendous overhead. This leaves only carving up each database into table-sets, prefixed or postfixed with some identifier (say, the business' ID) to achieve a common interface that will be programmable against and accessible.
In case my communication is poor, let me illustrate:
Say businesses using my software like to keep a record of their customers. I would have one table, for each business, titled "tblCustomer" and postfixed with the business' ID. I.E.:
For any particular business, its dataset would be the set of all tables common to all businesses, but restricted to those which are postfixed with its ID (presumably contained in some master table). I.E.:
and so on...
(these would form a distinct dataset logically and physically separate from business 123's data, which would be the same tables above, with 001 replaced by 123)...
My question is: Am I going about this the right way? If so, how do I create stored procedures which, given the business ID, will programmatically access the correct tables? Creating SQL statements from .NET would be simple, as the commands (Select, insert, update, etc.) are constructed programmatically anyway. A stored procedure, to my knowledge, cannot be....
Oh yes. I forgot to mention that I have thought of using the same "base name" + postfixed ID scheme to create copies of the stored procedures, but this seems wasteful. It may not be or it may be the only way... Thoughts?
Any help is appreciated. I hope joining this community will prove worthwhile!