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?