A set of tables for each user vs one set of large tables

[ulas]

Hi,

This is a pretty basic question I guess but it is one that I cannot decide one way or another on so I thought I should consult people who know much more about databases than me :)

So the problem I am looking at is this - there is a set of tables, around 20-30, which are used by an application with a single user. I am going to be working on converting this application into a multi-user system where each user, in essence, will be using the system independently. This means I need to either create a fresh set of tables for each user when they register to use the system, or I should add a column to each table to filter rows by user_id. I would imagine the number of users will be less than a 1000 and the highest number of rows each user needs in a given table will also be on the same magnitude though these numbers are liable to grow if the system becomes succesful.

I know that the structure of the database should ideally be constant but at the same time I am worried about locks happening since the users are likely to use the system at the same time.

FWIW I am planning on using SQL Server 2008 for this application. Any ideas, hints?

Thank you very much ahead of time and please let me know if anything sounds unclear ;)

Cheers!

ulas

[iburyak]

Create a user_id column for sure.
Can you imagine creating a new set of tables for each user instead of just inserting some records into a table?
You can create an index over the user_id column or combine it with another unique id in this table to make it even more robust. No blocks should be happening if each user should go after his own data only. Server 2008 should be so fast that even if users would go after the same data it should be no problem at all.


Good Luck.

[ulas]

Thank you so much for the response. As I said earlier, DBMSs are not my specialty so even something as basic as this can raise a question flag (well, may be basic but has huge implications since it affects the design :)).

My main worry was table level locks during (at least) UPDATEs to the table but since that does not seem to be the case, I feel much better about this option.

Thanks again!

ulas

[ck9663]

You might also consider enforcing referential integrity.

-- CK

[ulas]

You might also consider enforcing referential integrity.

-- CK

Do you mean in terms of just the userID columns that I will be adding or in general? I will definitely be enfording referential integrity as this makes logical sense to me in general but what kind of gains am I looking at by enabling this (purely a question of educational pursuit here ;)) ?

[ck9663]

So that all the values in UserID on the transaction tables should always be in your UserID reference/lookup table. No need to handle in front-end. This will prevent any orphan record.

-- CK

[ulas]

So that all the values in UserID on the transaction tables should always be in your UserID reference/lookup table. No need to handle in front-end. This will prevent any orphan record.

-- CK

Oh right right - I meant in terms of performance so I misunderstood you slightly. Thank you for the pointer though :)