SQL Server Performance Forum – Threads Archive

Schema per user or userid in primary key ?

I’ve got the following scenario: Have 10 – 30 user specific tables. Can have 5 – 50 users. A user can have up to 1 million records for some tables. So must I add a user_id to the primary key of the table. That mean that that table can have 50 million record in it. Or is it better to create a schema for each user. Thus having maybe 30 * 50 tables in the db. Anybody got a suggestion ? Thanks Karen PS. I’m a newbie, so please go slow !! <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

I’m go with one set of tables which contains the user_id, and cluster the user_id to ensure fast access to the rows of each user (that is if queries mostly go by user_id).

Agree with Bambola, I’d also make sure that userid is the leading column in the clustered index for concurrency and performance reasons. The userid leading column will group data for a user close together, on the same pages rather than spread throughout the table This would mean that other selects would need to have non-clustered indexes created for them, unless all selects are by user Cheers
Twan

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by karen99 I’ve got the following scenario: Have 10 – 30 user specific tables. Can have 5 – 50 users. A user can have up to 1 million records for some tables. So must I add a user_id to the primary key of the table. That mean that that table can have 50 million record in it. Or is it better to create a schema for each user. Thus having maybe 30 * 50 tables in the db. Anybody got a suggestion ? Thanks Karen PS. I’m a newbie, so please go slow !! <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> <hr height="1" noshade id="quote"></blockquote id="quote"> Same question as on SQL Server Central? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Well, I must correct my answer from there a little bit. Still I think only one schema should be used, but I would also opt for including user_id in a clustered index. But what actually confuses me, is your statement that these are user tables. Commonly a database is meant for sharing data, not for storing data in one place. What are you trying to achieve? Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

]]>