Database Design. Need Advice. Thank You.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Database Design. Need Advice. Thank You.

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-10-19 : 17:17:06

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

Kristen
Test

22859 Posts

Posted - 2007-10-20 : 02:22:03

"I am using UniqueIdentifier as Primary Keys"

You need to use Sequential GUIDs in SQL 2005 - otherwise the random-insert nature of GUIDs will adversely effect your PK fragmentation.

I'm not sure about using GUIDs for all your PKs. There are some very low volume tables, or multi-part keys that might lend themselves to an alternative - Categories for example.

I suppose part of the consideration is what the URLs need to look like. If the Category is URL-safe and human-readable that might be more enticing - but you may want to allow that to change, so still use surrogate PK. But how many categories would you have? I think an IDENTITY there might be better. Unless you wan to be able to merge category data from other dis-connected systems in the future.

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-10-20 : 06:35:12

The two main reasons why I am using Guids are:

- In the future I might manipulate data from tables. As you said merge data.
- I can use the Guid values, which are normalized in my ASP.NET project. For example, naming files after it.

Because these are important I am closer to use Guids than Ints for keys.

I have been reading the following articles:
http://www.codinghorror.com/blog/archives/000817.html
http://www.sql-server-performance.com/articles/per/guid_performance_p1.aspx

About sequencial guids you mean doing something like:


create table dbo.Posts
(
	PostID uniqueidentifier not null 
	  constraint PK_Post primary key clustered DEFAULT NewSequentialID(),

and


-- PostsCategories ...
create table dbo.PostsCategories
(
  PostID uniqueidentifier not null,
  CategoryID uniqueidentifier not null,
    constraint PK_PostsCategories
      primary key clustered (PostID, CategoryID) DEFAULT NewSequentialID(),

And about the fact that, for example, a Comment should be associated only to one Post or File and in my design it can be associated to various Posts and Files just like a Tag and Category, should I do something about this?

Thanks,
Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources