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 setup

I have an application that lets users upload pictures. These pictures are
stored in different folders depending on the user ID (of the user that it
belongs to). The path can be figured out dynamically by knowing the user
ID. However, the programmer that's coding my application has decided to
save the full path into the database.
Upside that I see is, if there's a huge load of traffic I can see how it may
be good for performance, since you don't need to dynamically figure out the
path.
The downside though, is that this leads to database bloat, and I'm not sure
if that will cause the database performance to decrease. Also, whenever the
system path is updated, a script needs to go through the database and update
everything to reflect the new path.
So, if you can give me your input on this, I'd greatly appreciate it. Is
this a good, or bad way of designing a database?Sounds like you only need to store the full path once per user. It seems
sensible to put the path in a Users table in your database. Storing the full
path against every picture would be inefficient and also may be inconvenient
if you ever need to change the file location.
--
David Portas
SQL Server MVP
--|||I agree with David, Save the directory in the User row... If you figure our
the path on the fly based on the userid, your are locked into the file
location. If you simply store it in the database, you can change it very
easily.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> I have an application that lets users upload pictures. These pictures are
> stored in different folders depending on the user ID (of the user that it
> belongs to). The path can be figured out dynamically by knowing the user
> ID. However, the programmer that's coding my application has decided to
> save the full path into the database.
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
> The downside though, is that this leads to database bloat, and I'm not
sure
> if that will cause the database performance to decrease. Also, whenever
the
> system path is updated, a script needs to go through the database and
update
> everything to reflect the new path.
> So, if you can give me your input on this, I'd greatly appreciate it. Is
> this a good, or bad way of designing a database?
>
>|||"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
is the root of all evil.
Make it work right, then make it work fast (or small). It's surprising how
infrequently the second step is required.|||> Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
> is the root of all evil.
> Make it work right, then make it work fast (or small). It's surprising how
> infrequently the second step is required.
I've heard different schools of thought though. One says that hardcoding
the path makes it quicker because #1, there's no need for an additional
query to get the path variables, and #2, there's no additional processing
needed to put it all together. And if an update were ever needed all I need
is one query to update them all.
However, it does add to the database bloat. Does that affect performance?|||> However, it does add to the database bloat. Does that affect performance?
Yes, which is why your argument doesn't make much sense.
--
David Portas
SQL Server MVP
--|||> Yes, which is why your argument doesn't make much sense.
Can you show me an article that talks about this downside?

Database Design setup

I have an application that lets users upload pictures. These pictures are
stored in different folders depending on the user ID (of the user that it
belongs to). The path can be figured out dynamically by knowing the user
ID. However, the programmer that's coding my application has decided to
save the full path into the database.
Upside that I see is, if there's a huge load of traffic I can see how it may
be good for performance, since you don't need to dynamically figure out the
path.
The downside though, is that this leads to database bloat, and I'm not sure
if that will cause the database performance to decrease. Also, whenever the
system path is updated, a script needs to go through the database and update
everything to reflect the new path.
So, if you can give me your input on this, I'd greatly appreciate it. Is
this a good, or bad way of designing a database?
Sounds like you only need to store the full path once per user. It seems
sensible to put the path in a Users table in your database. Storing the full
path against every picture would be inefficient and also may be inconvenient
if you ever need to change the file location.
David Portas
SQL Server MVP
|||I agree with David, Save the directory in the User row... If you figure our
the path on the fly based on the userid, your are locked into the file
location. If you simply store it in the database, you can change it very
easily.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...
> I have an application that lets users upload pictures. These pictures are
> stored in different folders depending on the user ID (of the user that it
> belongs to). The path can be figured out dynamically by knowing the user
> ID. However, the programmer that's coding my application has decided to
> save the full path into the database.
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
> The downside though, is that this leads to database bloat, and I'm not
sure
> if that will cause the database performance to decrease. Also, whenever
the
> system path is updated, a script needs to go through the database and
update
> everything to reflect the new path.
> So, if you can give me your input on this, I'd greatly appreciate it. Is
> this a good, or bad way of designing a database?
>
>
|||"Shabam" <blislecp@.hotmail.com> wrote in message
news:G5Gdnfwf89eRJsvcRVn-ow@.adelphia.com...

> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
is the root of all evil.
Make it work right, then make it work fast (or small). It's surprising how
infrequently the second step is required.
|||> Two good rules of thumb: 1) Don't duplicate, and 2) Premature optimization
> is the root of all evil.
> Make it work right, then make it work fast (or small). It's surprising how
> infrequently the second step is required.
I've heard different schools of thought though. One says that hardcoding
the path makes it quicker because #1, there's no need for an additional
query to get the path variables, and #2, there's no additional processing
needed to put it all together. And if an update were ever needed all I need
is one query to update them all.
However, it does add to the database bloat. Does that affect performance?
|||> However, it does add to the database bloat. Does that affect performance?
Yes, which is why your argument doesn't make much sense.
David Portas
SQL Server MVP
|||> Yes, which is why your argument doesn't make much sense.
Can you show me an article that talks about this downside?

Database Design

I have an application that lets users upload pictures. These pictures are
stored in different folders depending on the user ID (of the user that it
belongs to). The path can be figured out dynamically by knowing the user
ID. However, the programmer that's coding my application has decided to
save the full path into the database.

Upside that I see is, if there's a huge load of traffic I can see how it may
be good for performance, since you don't need to dynamically figure out the
path.

The downside though, is that this leads to database bloat, and I'm not sure
if that will cause the database performance to decrease. Also, whenever the
system path is updated, a script needs to go through the database and update
everything to reflect the new path.

So, if you can give me your input on this, I'd greatly appreciate it. Is
this a good, or bad way of designing a database?Hi

So long as you are not storing the image in the database, the actual
difference in size will be (say tens) of bytes per row. I would not say that
this is going to cause significant bloat.

One advantage will be that you are no-longer id dependent so users can share
images easily and there is no need to move any files if a user changes Id
for some reason.

You could also argue that if you ever decided to move the files to a
different drive, then it would be easier with the full filename as it is one
one update statement and no code changes, especially if you change the
directory structure where have half your users on one disc and the rest on
another.

John

"Shabam" <blislecp@.hotmail.com> wrote in message
news:wMCdnfvJofFkJ8vcRVn-oA@.adelphia.com...
> I have an application that lets users upload pictures. These pictures are
> stored in different folders depending on the user ID (of the user that it
> belongs to). The path can be figured out dynamically by knowing the user
> ID. However, the programmer that's coding my application has decided to
> save the full path into the database.
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
> The downside though, is that this leads to database bloat, and I'm not
sure
> if that will cause the database performance to decrease. Also, whenever
the
> system path is updated, a script needs to go through the database and
update
> everything to reflect the new path.
> So, if you can give me your input on this, I'd greatly appreciate it. Is
> this a good, or bad way of designing a database?|||Hi Shabam

Please do no post the same question separately in multiple newsgroups .

John

"Shabam" <blislecp@.hotmail.com> wrote in message
news:wMCdnfvJofFkJ8vcRVn-oA@.adelphia.com...
> I have an application that lets users upload pictures. These pictures are
> stored in different folders depending on the user ID (of the user that it
> belongs to). The path can be figured out dynamically by knowing the user
> ID. However, the programmer that's coding my application has decided to
> save the full path into the database.
> Upside that I see is, if there's a huge load of traffic I can see how it
may
> be good for performance, since you don't need to dynamically figure out
the
> path.
> The downside though, is that this leads to database bloat, and I'm not
sure
> if that will cause the database performance to decrease. Also, whenever
the
> system path is updated, a script needs to go through the database and
update
> everything to reflect the new path.
> So, if you can give me your input on this, I'd greatly appreciate it. Is
> this a good, or bad way of designing a database?