Design: Distinct Datasets

[devin.lusby]

Greetings Daniweb!

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.:
tblCustomer001
tblCustomer111
tblCustomer123

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.:

tblCustomer001
tblVehicle001
tblRepairOrder001
tblEstimate001
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....

:::Begin Edit:::
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?

:::End Edit:::

Any help is appreciated. I hope joining this community will prove worthwhile!
-Devin

[tesuji]

Hi Devin

This is quite a poor idea of designing data models. You will never be able to create good SQL select statements. As you already mentioned, creating and handling stored procedures obviously dwindles into complex task. How do you want to compare data of different customers which are stored in various databases (not tables), ever tried to connect to, say 10 or more databases simultaneously?? In such datamodel, virtually all needed SQL statements must be dynamically generated at runtime. That's rather circumstantially.

Better way would be designing relational data by means of Codd's nomalization theory, applying your tables with proper primary and foreign keys to assure unique records and referential data integrity. Then you will have only ONE customer and only ONE vehicle table. Customer would be identified by a customer ident, what might be generated automatically by database system. If a customer has zero to many vehicles, simple add customerID to vehicles and make it part of vehicle's primary key. The creating statements would then look like:

create table customer (customerID integer not null, 
   name varchar(100) not null,
   address varchar(50), primary key (customerID));
create table vehicle (vehiclesID integer not null, 
   customerID integer not null, brand varchar(50) not null,
   primary key (vehiclesID, customerID), 
   foreign key (customerID) references customer);

On that simple data model it's absolutely easy to find all customer living in New York an their vehicles, for example:

Select name, brand from customer join vehicle where address like '%New York%'

If you want to get horrified by your data model in question, try to find all customer living in NY by selecting that information from your datamodel (don't try it, it's quite impossible)

krs,
tesu

edit: sure, your sea of stored procedures could also be generated automatically at runtime like most other database objects (but it wouldn't make sense for your case)

[devin.lusby]

I'm afraid I didn't quite explain myself clearly. What I'm trying to say is that when one business is running the app and looking at their list of customers, they should only be looking at *THEIR* customers... Not the customers of any other businesses using my app.

Let's take the simple SELECT * FROM tblCustomers statement. If Business A looks at their customer list like this, and all customers for the entire application (across all businesses) are kept in a single table, business A will get their customers, as well as those from businesses B, C, D and so on....

I could use WHERE statements to restrict searches against tables to customers of business A only, but that would mean a column in almost every table dedicated to indicating which business the record pertains to. This seems ridiculous.

Business A's records need to be separate from business B's records and business C's records. Why, you might ask? For one, ease of selection. It seems a poor choice to include where statements in _EVERY_ select statement, just for this purpose. Second? It just makes sense. Third? Identity fields, such as voucher numbers, need to be unique within the scope of a business, not within the scope of *ALL* businesses. (I.E. Business A needs contiguous voucher numbers, 1, 2, 3, 4, ... n, instead of the completely random distribution that would be had if business B were also using the table for its records)...

I understand normalization up to the 3NF quite well. That is not at issue here. I can ensure referential and entity integrity no problem, so long as I find a way for my application to perform the correct operations based on which business is requesting functionality. (If business A adds a customer, it should be added to business A's customer table. If business B also does so, it should be added to its own customer table. Customers from both businesses should not be present in the same table)...

Have I confused the issue more or shed some clarifying light on it?
-Devin

[tesuji]

Hi again

I have understood you completely. You may study ERP systems (Enterprise Resource Planning) how they handle such problem of separating various customers strictly. There are famous ERP from Oracle and SAP. I myself do database design and programming on the field of SAP R/3 (has over 17000 tables). No matter how many various customers and their associated enterprise information is managed with R/3 customers etc are all stored in ONE table (there are companies currently having stored over five millions customers in one r/3 table). You can also adopt ERP system concept of client-capable software: Each table out of the 17000 tables has an additional field called client number what is integral part of the primary key. Also every where clause contains that client number. You should consider that being client-capable is a very important feature of ERP systems.

I would never sacrifice the great advantage of having only one set of relational tables for various businesses for the cursory benefit of saving just a where clause.

krs,
tesu