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

hi, I'm stuck with database design, and come to know of this forum, hope the experts here can provide me an advice.

I'm trying to design a database, to store the following type of information.

- Person (name, identity no...)
- Phone (phone type (mobile, residential, fax), number
- Address (street, country...)
- Person and phone relationship. A person can have 0 or more Phone, and the relationship can be user or subscriber.
- Person and address relationship. A person can live in 0 or more address, and the relationship can be occupants, owner...
- Phone and address relationship. A phone can be have 0 or more address, and the relationshp is installation address, billing address

My design forms a circle, person - person_phone- phone - phoneAddress - Address - personAddress (-back to person). Is this a bad design, if it is, any solution to overcome?I see nothing wrong with the circular design, since the intermediary table in your many-to-many relationships will prevent relational integrity issues cascading around your design.

A few questions, though...

Can a phone have more than one User?
Can a phone have more than one Subscriber?
Can a phone have more than one Installation Address?
Can a phone have more than one Billing Address?
Are these the only two types of Phone addresses, or are there potentially an unlimited number of address types for phones?

blindman|||thanks blindman for the assurance... coz I'm rather confuse, as I'm not sure will there be any hidden problems with this type of design.

1. Can a phone have more than one User? yes
2. Can a phone have more than one Subscriber? at any one time, no... but the number can be re-use (eg, the first subscriber unsubscribe, and another one subscribe to it)
3. Can a phone have more than one Installation Address? similar to 2
4. Can a phone have more than one Billing Address? similar to 2
5. Are these the only two types of Phone addresses, or are there potentially an unlimited number of address types for phones? might have more, but not identified as of now.

will this question affect my design?|||There are some alternative designs for this type of modeling, but it looks to me like you are going to require all the flexibility in the circular schema you have. That just means you will need to store more data-integrity logic in the procedures you write for inserting, updating, and deleting records.

See if anybody else has comments on this over the weekend. Otherwise, go with it.

blindman

Database Design for Catalog Management

Hi,

I need advice on my design.

I'm developing a system that track the catalog distribution.

I have a 5 table.

Company Table - Store list of company. Each company can have many staff.

Staff Table - Store list of staff.

Catalogs Table - Store list of catalog. Each catalog can have many revision.

PKEY id

name

latest

CatalogsRev Table - Store list of catalog revision.

PKEY catalogID -> Catalogs.id

PKEY year

Staff_Catalog Table - Each staff can have many catalog revision.

PKEY staffID -> Staff.id

PKEY catalogID -> CatalogsRev.catalogID

PKEY year -> CatalogsRev.year

Is the above table good enough to track which company do not have latest catalog revision?

For the Catalogs Table should I use a trigger to update the Catalogs.latest column everytime a new CatalogsRev is entered?

I've read some article on the web says trigger is slow, but in my situation is it suitable to use trigger?

Thanks,

Max

Hi
if u feel u r database satisifes 3rd normal form abs u r database is ok.SO u have to decide u r tables are going to satisify the normalization or not. Ok

U can write a trigger to modify the catalog information. Here u r modifying a column value i think it will not affect on performanace. I think so.

Thank u
Baba

Database Design Advice

Hello all,
I am looking for some advice on the design of a database for a
particular application I am tasked with. The database is part of an
equipment monitoring application that records numerous equipment data
readings and stores them to a database. The readings are taken every
few seconds and are stored for performance analysis and trending.
Needless to say the data table can get very large over time.
There is a web component that will allow users to save custom filtered
views of the data on particular types of data or specific date ranges.
I want to avoid dynamic SQL at all costs, so I was thinking of
generating a new SQL Server View for each user's custom view.
Here is the tough part - I need to have a summary page of all the user
defined views that shows the number of data points in each view. I
don't know how to do this without using dynamic SQL. For example, if
users create View1, View2, View3, and View4 - how can I have a single
stored proc that returns the count of each of them? The only thing I
can think of is having a cursor that goes through a "UserViews" table
and for each View, runs a dynamic SQL query like SELECT Count(DataID)
from "viewname", on each view. That seems like it would be horribly
inefficient.
Is there a better way getting this summary data? I hope I have
explained my problem well enough. Your advice is greatly appreciated."Yofnik" <yofnik@.comcast.net> wrote in message
news:1127420944.523072.102240@.f14g2000cwb.googlegroups.com...
> Hello all,
> I am looking for some advice on the design of a database for a
> particular application I am tasked with. The database is part of an
> equipment monitoring application that records numerous equipment data
> readings and stores them to a database. The readings are taken every
> few seconds and are stored for performance analysis and trending.
> Needless to say the data table can get very large over time.
> There is a web component that will allow users to save custom filtered
> views of the data on particular types of data or specific date ranges.
> I want to avoid dynamic SQL at all costs,
You shouldn't. There's really no such thing as dynamic SQL. All SQL is
treated the same by the server. It's compiled, optimized and cached. If
the same SQL is issued again, the cached plan is used.
See Generally
http://www.sommarskog.se/dynamic_sql.html
And you should understand how the query processor works.
Microsoft SQL Server Query Processor Internals and Architecture
http://msdn.microsoft.com/library/d... />
erproc.asp
Especially when you need to hard-code parameter values and when you need to
use parameter markers.

>so I was thinking of
> generating a new SQL Server View for each user's custom view.
>
Don't do that. Just create a table to store the SQL of the custom queries.
It's user data, not metadata.

> Here is the tough part - I need to have a summary page of all the user
> defined views that shows the number of data points in each view. I
> don't know how to do this without using dynamic SQL. For example, if
> users create View1, View2, View3, and View4 - how can I have a single
> stored proc that returns the count of each of them? The only thing I
> can think of is having a cursor that goes through a "UserViews" table
> and for each View, runs a dynamic SQL query like SELECT Count(DataID)
> from "viewname", on each view. That seems like it would be horribly
> inefficient.
>
Yes it would, but how else would you know how many rows a query returns. If
you must have this functionality, then you could run the registered queries
periodically and update a Rows column in the queries table.
David|||David,
Thanks for your reply. So, if I understand you correctly, you are
saying that in this case dynamic SQL is the right choice? If I store
the SQL and dynamically run it, won't it get recomiled each time? Then
if I want to do the summary, will each one be compiled every time the
summary is requested?
Thanks again|||"Yofnik" <yofnik@.comcast.net> wrote in message
news:1127423579.533176.322160@.g44g2000cwa.googlegroups.com...
> David,
> Thanks for your reply. So, if I understand you correctly, you are
> saying that in this case dynamic SQL is the right choice? If I store
> the SQL and dynamically run it, won't it get recomiled each time?
No. It won't. This is one of the most common misconceptions about dynamic
SQL. The details are in the link about query processor internals.

>Then
> if I want to do the summary, will each one be compiled every time the
> summary is requested?
You probably don't want to run every query each time the summary is
requested. Either drop that feature, or run
'select count(*) from (' + sql + ') dt'
for each stored query once a day or something.
David