Data sotrage design question

[kpg]

Hi all,

I have a general design question about a data storing asp.net website I'm
contemplating.

The site will allow users (businesses) to log in and manipulate client
records; add, edit, delete. My design question is this: Should the data
for all the different businesses be in a single database or should I have a
separate sql database for each.

Before applying to much thought to it I assumed the data would be in one
table: clients. Then the thought of different businesses, each unaware of
the other, having their data mingle, as it were, in a single table, for
some reason made me nervous.

So I could add a new client table for each business as they create an
account with me. Of course the end product will have several tables, not
just a client table. This approach also requires that the tables have
unique names, most likely the user's account number with a table suffix.

An alternative is to have a separate database file for each business, then
the table names could all be the same, just the database name is different.
I like this idea because it would allow a copy of the file to be given to
the customer if they ever wanted their data (for use elsewhere).

The problem is I will be hosting this site on some commercial third party
server and I don't know how much freedom will be given to me to create an
unlimited number of databases.

Does anyone know how existing large data based web sites organize the user
data? What there pros and cons.

Thanks,

kpg

 

[Miro]

There is a third option you are not considering.

1 database and every table has a column called "COMPANYCODE/ID".

When a user logs in, they log in to their company thus you have a CompanyID.

Every SQL statement you write - make sure you put "where companycode =
companyID"

That might be a solution for you.

 

[Mr. Arnold]

Hi all,

I have a general design question about a data storing asp.net website I'm
contemplating.

The site will allow users (businesses) to log in and manipulate client
records; add, edit, delete. My design question is this: Should the data
for all the different businesses be in a single database or should I have a
separate sql database for each.

The example I have seen, all client data is kept in one database.

Before applying to much thought to it I assumed the data would be in one
table: clients. Then the thought of different businesses, each unaware of
the other, having their data mingle, as it were, in a single table, for
some reason made me nervous.

So I could add a new client table for each business as they create an
account with me. Of course the end product will have several tables, not
just a client table. This approach also requires that the tables have
unique names, most likely the user's account number with a table suffix.

It's maintenance nightmare. The example I have seen, all data by client
is kept in the same tables.

An alternative is to have a separate database file for each business, then
the table names could all be the same, just the database name is different.
I like this idea because it would allow a copy of the file to be given to
the customer if they ever wanted their data (for use elsewhere).

Yeah OK....

The problem is I will be hosting this site on some commercial third party
server and I don't know how much freedom will be given to me to create an
unlimited number of databases.

Does anyone know how existing large data based web sites organize the user
data? What there pros and cons.

The key to it I think is to design the application so that security and
data access is by client and user within client. The business layer
should be security aware by client and user within client.