SQL Server Performance

Need advice on database. Thank You.

Discussion in 'SQL Server 2005 General Developer Questions' started by shapper, Oct 19, 2007.

  1. shapper New Member

    Hello,

    I am creating a database where:
    - I have a Blogs and Folders system.
    - Use a common design so I can implement new systems in the future.

    Users, Comments, Ratings, View, Tags and Categories are tables common to all systems, i.e., used by Posts and Files in Blogs and Folders.

    - One Tag or Category can be associated to many Posts or Files.
    - One Comment, View or Rating should be only associated to one Post or one File. I am missing this ... (1)

    Relations between a File / Folder and Comments / Ratings / View / Tags / Categories are done using FilesRatings, FoldersViews, etc.

    I am using UniqueIdentifier as Primary Keys.
    I checked ASP.NET Membership tables, a few articles and few features in my project, such as renaming files with the GUID of their records.
    I didn't decided yet for INT or UNIQUEIDENTIFIER.

    I am looking for some feedback on the design of my database.
    One thing I need to improve is mentioned in (1)

    Thank You,
    Miguel

    My Database Script:

    -- Users ...
    create table dbo.Users
    (
    UserID uniqueidentifier not null
    constraint PK_User primary key clustered,
    [Name] nvarchar(200) not null,
    Email nvarchar(200) null,
    UpdatedDate datetime not null
    )

    -- Categories ...
    create table dbo.Categories
    (
    CategoryID uniqueidentifier not null
    constraint PK_Category primary key clustered,
    [Name] nvarchar(100) not null
    )

    -- Comments ...
    create table dbo.Comments
    (
    CommentID uniqueidentifier not null
    constraint PK_Comment primary key clustered,
    AuthorID uniqueidentifier not null,
    Title nvarchar(400) null,
    Body nvarchar(max) null,
    UpdatedDate datetime not null,
    constraint FK_Comments_Users
    foreign key(AuthorID)
    references dbo.Users(UserID)
    )

    -- Ratings ...
    create table dbo.Ratings
    (
    RatingID uniqueidentifier not null
    constraint PK_Rating primary key clustered,
    AuthorID uniqueidentifier not null,
    Value float not null,
    constraint FK_Ratings_Users
    foreign key(AuthorID)
    references dbo.Users(UserID)
    )

    -- Tags ...
    create table dbo.Tags
    (
    TagID uniqueidentifier not null
    constraint PK_Tag primary key clustered,
    [Name] nvarchar(100) not null
    )

    -- Views ...
    create table dbo.Views
    (
    ViewID uniqueidentifier not null
    constraint PK_View primary key clustered,
    Ticket [datetime] not null
    )

    -- Blogs ...
    create table dbo.Blogs
    (
    BlogID uniqueidentifier not null
    constraint PK_Blog primary key clustered,
    Title nvarchar(400) null,
    Description nvarchar(2000) null,
    CreatedDate datetime null
    )

    -- Posts ...
    create table dbo.Posts
    (
    PostID uniqueidentifier not null
    constraint PK_Post primary key clustered,
    BlogID uniqueidentifier not null,
    AuthorID uniqueidentifier not null,
    Title nchar(1000) null,
    Body nvarchar(max) null,
    UpdatedDate datetime not null,
    IsPublished bit not null,
    constraint FK_Posts_Blogs
    foreign key(BlogID)
    references dbo.Blogs(BlogID)
    on delete cascade,
    constraint FK_Posts_Users
    foreign key(AuthorID)
    references dbo.Users(UserID)
    on delete cascade
    )

    -- PostsCategories ...
    create table dbo.PostsCategories
    (
    PostID uniqueidentifier not null,
    CategoryID uniqueidentifier not null,
    constraint PK_PostsCategories
    primary key clustered (PostID, CategoryID),
    constraint FK_PostsCategories_Posts
    foreign key(PostID)
    references dbo.Posts(PostID)
    on delete cascade,
    constraint FK_PostsCategories_Categories
    foreign key(CategoryID)
    references dbo.Categories(CategoryID)
    )

    -- PostsComments ...
    create table dbo.PostsComments
    (
    PostID uniqueidentifier not null,
    CommentID uniqueidentifier not null,
    constraint PK_PostsComments
    primary key clustered (PostID, CommentID),
    constraint FK_PostsComments_Posts
    foreign key(PostID)
    references dbo.Posts(PostID)
    on delete cascade,
    constraint FK_PostsComments_Comments
    foreign key(CommentID)
    references dbo.Comments(CommentID)
    on delete cascade
    )

    -- PostsRatings ...
    create table dbo.PostsRatings
    (
    PostID uniqueidentifier not null,
    RatingID uniqueidentifier not null,
    constraint PK_PostsRatings
    primary key clustered (PostID, RatingID),
    constraint FK_PostsRatings_Posts
    foreign key(PostID)
    references dbo.Posts(PostID)
    on delete cascade,
    constraint FK_PostsRatings_Ratings
    foreign key(RatingID)
    references dbo.Ratings(RatingID)
    on delete cascade
    )

    -- PostsTags ...
    create table dbo.PostsTags
    (
    PostID uniqueidentifier not null,
    TagID uniqueidentifier not null,
    constraint PK_PostsTags
    primary key clustered (PostID, TagID),
    constraint FK_PostsTags_Posts
    foreign key(PostID)
    references dbo.Posts(PostID)
    on delete cascade,
    constraint FK_PostsTags_Tags
    foreign key(TagID)
    references dbo.Tags(TagID)
    )

    -- PostsViews ...
    create table dbo.PostsViews
    (
    PostID uniqueidentifier not null,
    ViewID uniqueidentifier not null,
    constraint PK_PostsViews
    primary key clustered (PostID, ViewID),
    constraint FK_PostsViews_Posts
    foreign key(PostID)
    references dbo.Posts(PostID)
    on delete cascade,
    constraint FK_PostsViews_Views
    foreign key(ViewID)
    references dbo.Views(ViewID)
    on delete cascade
    )

    -- Folders ...
    create table dbo.Folders
    (
    FolderID uniqueidentifier not null
    constraint PK_Folder primary key clustered,
    [Name] nvarchar(100) null,
    Description nvarchar(2000) null,
    CreatedDate datetime not null,
    URL nvarchar(400) not null
    )

    -- Files ...
    create table dbo.Files
    (
    FileID uniqueidentifier not null
    constraint PK_File primary key clustered,
    FolderID uniqueidentifier not null,
    AuthorID uniqueidentifier not null,
    Title nvarchar(400) null,
    Description nvarchar(2000) null,
    [Name] nvarchar(100) not null,
    URL nvarchar(400) not null,
    UpdatedDate datetime not null,
    IsPublished bit not null,
    Type nvarchar(50) null,
    constraint FK_Files_Folders
    foreign key(FolderID)
    references dbo.Folders(FolderID)
    on delete cascade,
    constraint FK_Files_Users
    foreign key(AuthorID)
    references dbo.Users(UserID)
    on delete cascade
    )

    -- FilesCategories ...
    create table dbo.FilesCategories
    (
    FileID uniqueidentifier not null,
    CategoryID uniqueidentifier not null,
    constraint PK_FilesCategories
    primary key clustered (FileID, CategoryID),
    constraint FK_FilesCategories_Files
    foreign key(FileID)
    references dbo.Files(FileID)
    on delete cascade,
    constraint FK_FilesCategories_Categories
    foreign key(CategoryID)
    references dbo.Categories(CategoryID)
    )

    -- FilesComments ...
    create table dbo.FilesComments
    (
    FileID uniqueidentifier not null,
    CommentID uniqueidentifier not null,
    constraint PK_FilesComments
    primary key clustered (FileID, CommentID),
    constraint FK_FilesComments_Files
    foreign key(FileID)
    references dbo.Files(FileID)
    on delete cascade,
    constraint FK_FilesComments_Comments
    foreign key(CommentID)
    references dbo.Comments(CommentID)
    on delete cascade
    )

    -- FilesRatings ...
    create table dbo.FilesRatings
    (
    FileID uniqueidentifier not null,
    RatingID uniqueidentifier not null,
    constraint PK_FilesRatings
    primary key clustered (FileID, RatingID),
    constraint FK_FilesRatings_Files
    foreign key(FileID)
    references dbo.Files(FileID)
    on delete cascade,
    constraint FK_FilesRatings_Ratings
    foreign key(RatingID)
    references dbo.Ratings(RatingID)
    on delete cascade
    )

    -- FilesTags ...
    create table dbo.FilesTags
    (
    FileID uniqueidentifier not null,
    TagID uniqueidentifier not null,
    constraint PK_FilesTags
    primary key clustered (FileID, TagID),
    constraint FK_FilesTags_Files
    foreign key(FileID)
    references dbo.Files(FileID)
    on delete cascade,
    constraint FK_FilesTags_Tags
    foreign key(TagID)
    references dbo.Tags(TagID)
    )

    -- FilesViews ...
    create table dbo.FilesViews
    (
    FileID uniqueidentifier not null,
    ViewID uniqueidentifier not null,
    constraint PK_FilesViews
    primary key clustered (FileID, ViewID),
    constraint FK_FilesViews_Files
    foreign key(FileID)
    references dbo.Files(FileID)
    on delete cascade,
    constraint FK_FilesViews_Views
    foreign key(ViewID)
    references dbo.Views(ViewID)
    on delete cascade
    )

    -- Run script
    go
  2. suresha_b New Member

    Change to primary key index to NON-clustered for uniqueidentifier type columns.
    For example: For the table Tags TagID should nonclustered and you can create a clustered index on [Name] column.
    create table dbo.Tags
    (
    TagID uniqueidentifier not null
    constraint PK_Tag primary key clustered,
    [Name] nvarchar(100) not null
    )

Share This Page