Database Design. Need Advice. Thank You.

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 think need to improve is mentioned in (1)
But any advices to improve it would be great.
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
goOn Oct 22, 12:21 am, shapper <mdmo...@.gmail.com> wrote:
> 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 think need to improve is mentioned in (1)
> But any advices to improve it would be great.
> 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
Please, anyone?
Does someone sees a way to improve this?
Thanks,
Miguel

Database design question

When you have user logins and want to restrict data access for every user only to data relevant or owned by them how do you implement this?

Example:
When a user logs on, he will only have access to data that he himself have created or that have reading rights to which he qualifies.

Lets say I have ten different tables and all data from all users are stored in these tables. Do I need an extra field in every table that denotes who has access to specific rows?

What is the most sensible way to implement this scenario?

We are using ASP.NET, C#, Windows Server 2003, SQL Server 2000

Thanks!

ValeinOne way to implement this is to have all requests for reading or writing data handled through Stored Procedures. Pass in the user information, and then you can have the stored procedures do whatever is needed to determine what data is appropriate to be viewed/modified.

You then give no rights to the underlying tables, just execute rights on the stored procedure.|||Hi Douglas and thanks for answering!

I still don't see how the stored procedures would be able to tell what data a certain user should be allowed to view/update unless access information is stored with the data in the tables.

If there are two articles in a table and a user should only be able to read one of them, how can a stored procedure know what article to return to the user unless every article has access information tagged to them?

Stian Danielsen|||Of course the stored procedure does not "know" what a given user should have access to, you need to add logic to allow that to happen in the stored procedure!

You build queries that, for instance, link the articles with the user table (you do have such a table, correct?) and so the user would only get articles they wrote (for editing, for instance). You need to do something more clever for letting folks read articles beyond what they have written, but again, that can be done with tables that relate articles to groups of users, for instance.|||Thanks again for replying.

Yes, I have a user table and yes, I obviously need to do logic in the stored procedures to make it work. Still, joining the user table with the articles table would require a relation between the two. Most likely a user id column or a user group id column (or both) in the articles table and perhaps also an access specifications column (denoting read and/or write access).

This is a solution, but in my case it does seem a bit cumbersome. See, I don't only have an articles table - I have about 6 or 7 tables that need such access control. Seems to me I need the access rights columns in every one of those tables in order to link the tables up with the user table.

My question was: Is this the way to do it, or is there a better way?
I cannot think of one at the moment.

Thanks again,

Valein|||::This is a solution, but in my case it does seem a bit cumbersome.

You could also have some hardware (named crystal ball) attached to the server and see whether the SP can work with this to gfind out which user can read thwhich data.

See, there HAS to be some tables to manage this information. THis is not cumbersome - this is rquired for anythig than a crystal ball.

::See, I don't only have an articles table - I have about 6 or 7 tables that need such access
::control.

THen make a security management subsystem and integrate the tables there :-)

::My question was: Is this the way to do it, or is there a better way?
::I cannot think of one at the moment.

ONLY way for now - properbly forever.

BTW - no need for a stored procedure, a view should be able to handle the read filtering, too :-) Easier to use.|||This may be too simplistic for your application, but it's one approach.

I do this with a session item for the uid captured during the login process. Each DB entry the user makes it stores the uid in the table. In the select statement it pulls just the records that contain the uid of the person logged in.

selectwhatever fromyourtable where uidcolumn='" & Session("uid") & "'",yourconnection)

|||Thanks a lot for all your replies guys!

It's great to know that the solution I was going for is the only one plausible.
I suspected as much, but it can't hurt to make sure, can it? :-)

Thanks again!

Valein

Database Design of Categories

I am creating a website that has categories such as:

Sports -> Soccer -> Soccer Shoes

I can not think of an effectively way to implement this into a database. Please help.

Here is a website that has a bunch of sample database designs. If you don't find exactly what you want, the process of looking thru the other designs should help you.

http://www.databaseanswers.org/data_models/

A good data modeling book is by David Hay, Data Modeling Patterns: Conventions of Thought. He gives sample high-level data designs and talks thru different modeling alternatives so you fully understand the implications of subtle modeling differences.

|||

If you post what you've tried and why you didn't like it, I'll be happy to give you ideas on how to improve it.

|||

Thanks, that is a really cool site! I haven't looked at all the database designs because there are a lot of them but one that I found is #39 Books and Libraries. They have a "categories" table but all it stores is the CategoryID and the CategoryName. With just that information how to do you know where the category is in the hierarchy of categories? For example, you have Literature -> Books -> Fiction, say Fiction has CategoryID 20, how do you know where Fiction is in the hierarchy just by knowing the CategoryID?

I came across aneBay developer site that explains how they store their categories. What they do is they store the CategoryID but also the ID of it's parent. This way you know where it exists in the hierarchy. This is what the table would look like:

Category
CategoryID
ParentID
CategoryName
IsLeafCategory

What does everyone think of this solution? Does anyone have any alternative solutions?

|||

A better column name for ParentID would be ParentCategoryID. It's always better to be very clear.

This is a more powerful model than Literature->Books->Fiction because it can handle a "infinite" number of hierarchical levels.

It is also a more complicated one to use than a model in which you force the same number of levels in the hierarchy by using a different table for each level in the hierarchy.

IsLeafCategory is a redundant piece of information in that it can be deduced when required. I would not make use of it unless you have a specific need for it (because then you have to write code to make sure it is always correct).