Post reply

Schema per user of userid in primary key ?

  • January 29, 2004 at 3:00 am

    #83379

    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

  • January 29, 2004 at 3:58 am

    #492136

    Although I don't have experience with 50 million rows in a table, I would choose one schema and add the user_id to the primary key. With a proper design, smart indexing strategy that should be manageable. Depending on the actual row size in those tables, you might want to consider using partitioned views or even distributed partitioned views.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 29, 2004 at 4:36 am

    #492137

    Why would I want to use partitioned views ?

    Sorry, I'm a newbee !

  • January 29, 2004 at 4:47 am

    #492138

    I just mentioned it, because it comes to mind when dealing with such a number of rows. Not sure if it applies to your environment.

    As a start, take a look at BOL for Partitioned Views or

    here

    http://www.sql-server-performance.com/q&a82.asp

    http://www.sql-server-performance.com/sql_2000.asp

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply