467,915 Members | 1,279 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

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

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
Sep 24 '08 #1
  • viewed: 1095
Share:
6 Replies
iburyak
Expert 512MB
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.
Sep 24 '08 #2
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
Sep 25 '08 #3
ck9663
Expert 2GB
You might also consider enforcing referential integrity.

-- CK
Sep 25 '08 #4
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 ;)) ?
Sep 25 '08 #5
ck9663
Expert 2GB
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
Sep 25 '08 #6
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 :)
Sep 25 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by Albretch | last post: by
1 post views Thread by wireless | last post: by
44 posts views Thread by Mariusz Jedrzejewski | last post: by
7 posts views Thread by ii2o | last post: by
4 posts views Thread by Joachim Klassen | last post: by
11 posts views Thread by dskillingstad | last post: by
10 posts views Thread by Jim Devenish | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.