MultiUsers Question

We had a debate on designing a database .The use case is;

an application that we will rent to other companies from our own datacenter on theweb.They will all use thesame database schema.We found 3 alternatives for our solution.But we are not sure which one is the best.

We are expecting 20.000 insurance agencies to use the database composed of 33 tables.And they will all have their unique data but sometimes for reporting we might union their data.

ALTERNATIVES

1.Make 20.000 database instances.Every agency will have their own database instance.

2.Make one database instance , put a database ownerID(int) field to every table.And use appropiate indexes.*or table partioning

3.Make one database instance , and create 33 tables for every database owner.So total number of tables in the datase equals to numberofUsers X 33

Did anyone tested this situation?

*an article I read on the web said partining is allowed to 1000 per table and it is slow in SQL 2005.(I am confused)

http://www.sqljunkies.com/Article/F4920050-6C63-4109-93FF-C2B7EB0A5835.scuk

I would choose #1 possibly if it would be important for backup purposes to keep the data "separate". Cross reporting would be difficult, but not totally impossible -- if you can work from a "snapshot". It would be easiest to run a process to pull the data you need from all the agencies into a singular table structure just for the report(s), that has the added OwnerID column. You could do this either as some nightly process, or place a trigger on each of the tables that need to be reported on to replicate the changes to the common table. It would be a completely redundant dataset, so the backup of it isn't all that important, other than the table structure, and any special views/stored procedures/extra tables you may have in there.

This approach also makes it a lot easier to insure that a mistake isn't made whereby another agencies data isn't presented to a different agency. If you have a DBA (Which I'm guessing you do not, or you are the DBA), then they should be able to provide you with views and stored procedures that the application programmers are allowed to use to access the data and hence shouldn't be able to get themselves into trouble.

This type of setup is a bit more complicated to set up, but, it does effectively partition the tables for cross-agency reports.

4. Make one database instance, put a database ownerID(int) field in one (or more) of the tables. ID's are global (across agencies), so that a ownerID is redundant in tables that use an ID (or GUID) as it's primary key that is created in a different table.

|||hmm I am sorry but I did not understand how your 4 th approach differs from the 2 nd approach?|||

First 20,000 insurance agencies is not many, if you take them to be users, it means you have 20,000 users. In the US insurance is divided into Property and Casuality for home and cars policies and Life and Health. So just put them into the type of policy sold or by location and it becomes just 20,000 users. I have told you before you need a DDL(data definition language) expert.

If you don't have it installed check the Pubs sample database for ideas if you decide to use location to separate them because car insurance is sold by state drivers license. Hope this helps.

|||

The 4th approach differs from the 2nd in that if you have say these two tables:

Users->UserID,Name, etc.
Policies->PolicyID(IDENTITY),OwnerID(a UserID),SalesmanID(a UserID),PolicyTypeID, etc.
PolicyRider->PolicyID,RiderTypeID,etc.

In approach 2, the tables would look like this:

Users->AgencyID,UserID,Name, etc.
Policies->AgencyID,PolicyID,OwnerID,SalesmanID,PolicyTypeID,etc
PolicyRider->AgencyID,PolicyID,RiderTypeID,etc.

Where in approach 4, the tables would look like this if you allow any user to belong to multiple Agencies:

Users->UserID,Name,etc
UserAgencies->UserID,AgencyID
Policies->AgencyID,PolicyID, ...
PolicyRider->PolicyID,RiderTypeID,etc (NOTE No AgencyID, because the PolicyRider record has a reference to the Policies table entry which already contains a AgencyID)

Or like this, if no Users can be in multiple agencies:

Users->AgencyID,UserID, ...
Policies->PolicyID, ... (NOTE No AgencyID because each policy is owned by a user, and each user can only be in a single agency, this policy must belong to the agency the user is in)
PolicyRider->PolicyRider->PolicyID,RiderTypeID,etc (NOTE No AgencyID, because the PolicyRider record has a reference to the Policies table entry which has a reference to a Users table entry which contains a AgencyID)

You may want to attach the AgencyID to the Policy instead of the User, in these cases, but the idea is that so long as the "parent" of any relationship already has an AgencyID, the children do not, since you can imply their AgencyID by referencing their (singular) parent's AgencyID.

|||

if no Users can be in multiple agencies:

Users->AgencyID,UserID, ...
Policies->PolicyID, ... (NOTE No AgencyID because each policy is owned by a user, and each user can only be in a single agency, this policy must belong to the agency the user is in)

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I have read for 4 times.But did not understand how will I know that a record is owned by that AGENCY or by that USER.

maybe it is too late and I am really tired...is there anyuserID on the Policies table?

PS:I really do wanna implement a simple accountability pattern for the user system but performant.So the users can be in agencies but I will define that by simple attributeTypes in database not using the relations...

|||Sorry, yes. I was assuming that the Policies table would also have a OwnerID field (that's a UserID) or an AgentID (also a UserID), or some other field that you would use that to tie the record back to a particular user which is then tied to an AgencyID.