Database structure decision

[Mikee Freedom]

Good Morning all,

New member to the list, hoping you might be able to give me some much
needed advice.

Basically, I have a client who would like to offer the ability for his
users to have their own independent website at his domain. It is not as
clear cut as that but as a generic description it will do.

I know such services exist and I'm by no means emulating there's in any
way. the specific purpose of the individual user sites is fairly
specific, hence why he needs to get us to create it for him.

In a nutshell, people will be able to sign up, make some configuration
decisions, add some content, and have a website of their own that they
will be able to upload photo's to. Lot's of photo's.

The decision I was looking at making, was whether or not to create
individual databases for each of the new users. If this was going to be
a good idea or bad, or if it was dependent a little on further factors.

I've only begun to plan the site but this idea popped in to my head and
I was hoping someone could either say - "you ass, what are you
thinking?"; or indicate it may be beneficial.

My alternate option is to relate all content, photo's, albums, etc to
individual users. This is cool I guess, but liked the idea of complete
seperation.

One specific question I had was, if I needed to search for a particular
value in multiple databases is this going to be a pain in the ass, a
terrible load on the server... or anything else that I may be
overlooking.

Conclusion :

I like the idea of it, is it a good one?
Are there considerations?

Thanks everyone,
Mikee

p.s. if any of what I've written doesn't make sense please feel free to
berate or ask for further explanation :)

[Geoff Berrow]

Re: Database structure decision

Message-ID: <1169763927.138 077.147690@m58g 2000cwm.googleg roups.comfrom
Mikee Freedom contained the following:

>
>One specific question I had was, if I needed to search for a particular
>value in multiple databases is this going to be a pain in the ass, a
>terrible load on the server... or anything else that I may be
>overlooking.

A relational database management system is designed and optimised to
handle related data. And you want to split it up into separate
databases because you 'like the idea'?

Beats me why you think that might be a smart move.

And /please/ learn how to use the apostrophe.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[AlterEgo]

Re: Database structure decision

Mikee,

I would put all users in the same database. Less maintenance, less problems.
You can always scale up or out if the site is successful. With multiple
databases, the more successful the site, the more maintenance and headaches
you'll have.

BTW, don't store the photos in the database, store them in the file system.
Use a hive folder structure based upon parts of the file name.

A
--AA
--AB
----ABA
----ABB
----ABC

Also, make sure you have a good backup and recovery plan for the DB and file
system as well.

-- Bill


"Mikee Freedom" <mikeefreedom@g mail.comwrote in message
news:1169763927 .138077.147690@ m58g2000cwm.goo glegroups.com.. .

Good Morning all,
>
New member to the list, hoping you might be able to give me some much
needed advice.
>
Basically, I have a client who would like to offer the ability for his
users to have their own independent website at his domain. It is not as
clear cut as that but as a generic description it will do.
>
I know such services exist and I'm by no means emulating there's in any
way. the specific purpose of the individual user sites is fairly
specific, hence why he needs to get us to create it for him.
>
In a nutshell, people will be able to sign up, make some configuration
decisions, add some content, and have a website of their own that they
will be able to upload photo's to. Lot's of photo's.
>
The decision I was looking at making, was whether or not to create
individual databases for each of the new users. If this was going to be
a good idea or bad, or if it was dependent a little on further factors.
>
I've only begun to plan the site but this idea popped in to my head and
I was hoping someone could either say - "you ass, what are you
thinking?"; or indicate it may be beneficial.
>
My alternate option is to relate all content, photo's, albums, etc to
individual users. This is cool I guess, but liked the idea of complete
seperation.
>
One specific question I had was, if I needed to search for a particular
value in multiple databases is this going to be a pain in the ass, a
terrible load on the server... or anything else that I may be
overlooking.
>
Conclusion :
>
I like the idea of it, is it a good one?
Are there considerations?
>
Thanks everyone,
Mikee
>
p.s. if any of what I've written doesn't make sense please feel free to
berate or ask for further explanation :)
>

[Jerry Stuckle]

Re: Database structure decision

Mikee Freedom wrote:

Good Morning all,
>
New member to the list, hoping you might be able to give me some much
needed advice.
>
Basically, I have a client who would like to offer the ability for his
users to have their own independent website at his domain. It is not as
clear cut as that but as a generic description it will do.
>
I know such services exist and I'm by no means emulating there's in any
way. the specific purpose of the individual user sites is fairly
specific, hence why he needs to get us to create it for him.
>
In a nutshell, people will be able to sign up, make some configuration
decisions, add some content, and have a website of their own that they
will be able to upload photo's to. Lot's of photo's.
>
The decision I was looking at making, was whether or not to create
individual databases for each of the new users. If this was going to be
a good idea or bad, or if it was dependent a little on further factors.
>
I've only begun to plan the site but this idea popped in to my head and
I was hoping someone could either say - "you ass, what are you
thinking?"; or indicate it may be beneficial.
>
My alternate option is to relate all content, photo's, albums, etc to
individual users. This is cool I guess, but liked the idea of complete
seperation.
>
One specific question I had was, if I needed to search for a particular
value in multiple databases is this going to be a pain in the ass, a
terrible load on the server... or anything else that I may be
overlooking.
>
Conclusion :
>
I like the idea of it, is it a good one?
Are there considerations?
>
Thanks everyone,
Mikee
>
p.s. if any of what I've written doesn't make sense please feel free to
berate or ask for further explanation :)
>

One thing to consider here - the users. They'll be uploading their own
content. Does this include server-side scripts like PHP, Perl, etc.?
Will they need to create their own tables for anything? Will different
users have vastly different requirements?

If so, I think you should go with separate databases for each user for
security purposes. Give each user their own userid and password and
only allow them access to their own database.

As for storing pictures in the database - I do it regularly. MySQL
handles it quite well. I use mainly the InnoDB engine, so I also have
foreign key restraints, which I set up to not allow a picture to be
deleted as long as it's still being referenced. It also makes it easier
to reference the pictures - you don't need a filename. I just keep the
pictures in their own table for performance reasons and don't worry
about it any more.

Not to mention making backups easier.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[AlterEgo]

Re: Database structure decision

Jerry,

Regarding storing images in the database:

1. If one is looking for quick and easy (as in a hobby application), then I
totally agree - store them in the database. If one needs to keep a scalable
product life-cycle in mind, then I would not keep them in the database.
2. If this is a commercial or community driven venture, then it will have to
scale if it is successful. If it is not successful, then it really won't
matter.
3. Transferring binary data from the native file system is way faster than
any SQL database.
4. File systems are more easily scaled than databases.
5. Automated image management utilities (for creating thumbnails, converting
image formats, reading image meta-data, etc.) love working with file
systems, but hate working with databases.
6. Its far easier to distribute images to the "edge of the web" with
companies like Akamai or Digital Island hosting the content close to the
users.

I guess what it really boils down to is: thousands of pictures or millions
of pictures? hi res, low res, thumbnails, etc.?

-- Bill


"Jerry Stuckle" <jstucklex@attg lobal.netwrote in message
news:w6OdnbneO9 eyGyTYnZ2dnUVZ_ uKknZ2d@comcast .com...

Mikee Freedom wrote:

>Good Morning all,
>>
>New member to the list, hoping you might be able to give me some much
>needed advice.
>>
>Basically, I have a client who would like to offer the ability for his
>users to have their own independent website at his domain. It is not as
>clear cut as that but as a generic description it will do.
>>
>I know such services exist and I'm by no means emulating there's in any
>way. the specific purpose of the individual user sites is fairly
>specific, hence why he needs to get us to create it for him.
>>
>In a nutshell, people will be able to sign up, make some configuration
>decisions, add some content, and have a website of their own that they
>will be able to upload photo's to. Lot's of photo's.
>>
>The decision I was looking at making, was whether or not to create
>individual databases for each of the new users. If this was going to be
>a good idea or bad, or if it was dependent a little on further factors.
>>
>I've only begun to plan the site but this idea popped in to my head and
>I was hoping someone could either say - "you ass, what are you
>thinking?"; or indicate it may be beneficial.
>>
>My alternate option is to relate all content, photo's, albums, etc to
>individual users. This is cool I guess, but liked the idea of complete
>seperation.
>>
>One specific question I had was, if I needed to search for a particular
>value in multiple databases is this going to be a pain in the ass, a
>terrible load on the server... or anything else that I may be
>overlooking.
>>
>Conclusion :
>>
>I like the idea of it, is it a good one?
>Are there considerations?
>>
>Thanks everyone,
>Mikee
>>
>p.s. if any of what I've written doesn't make sense please feel free to
>berate or ask for further explanation :)
>>

>
One thing to consider here - the users. They'll be uploading their own
content. Does this include server-side scripts like PHP, Perl, etc.? Will
they need to create their own tables for anything? Will different users
have vastly different requirements?
>
If so, I think you should go with separate databases for each user for
security purposes. Give each user their own userid and password and only
allow them access to their own database.
>
As for storing pictures in the database - I do it regularly. MySQL
handles it quite well. I use mainly the InnoDB engine, so I also have
foreign key restraints, which I set up to not allow a picture to be
deleted as long as it's still being referenced. It also makes it easier
to reference the pictures - you don't need a filename. I just keep the
pictures in their own table for performance reasons and don't worry about
it any more.
>
Not to mention making backups easier.
>
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Norman Peelman]

Re: Database structure decision

AlterEgo wrote:

Jerry,
>
Regarding storing images in the database:
>
1. If one is looking for quick and easy (as in a hobby application), then I
totally agree - store them in the database. If one needs to keep a scalable
product life-cycle in mind, then I would not keep them in the database.

Not sure that I agree with you on this point. You CAN overload a
filesystem with too many files in any one directory. You must then
employ a software storage scheme (layer) just to keep things managable.

2. If this is a commercial or community driven venture, then it will have to
scale if it is successful. If it is not successful, then it really won't
matter.

How does keeping them in the database not scale?

3. Transferring binary data from the native file system is way faster than
any SQL database.

I think that also depends. How many files in that 'full' directory?

4. File systems are more easily scaled than databases.

Managability (backups, keeping the database and filesystem in sync)
can become quite a bear. Keeping it all in the database means one backup
operation results in a complete entity that can be re-created without
problems. And, they can be zipped up to save space. You can achieve
zipped archives approx. 12% of the original size (that's including the
image data).

5. Automated image management utilities (for creating thumbnails, converting
image formats, reading image meta-data, etc.) love working with file
systems, but hate working with databases.

I don't know of any that are designed to work with databases. That
being said, I can load an image from a database, resize/manipulate it
with GD (maybe even with imagick - magicwand api) and send it out with
no problem, all without touching the filesystem.

6. Its far easier to distribute images to the "edge of the web" with
companies like Akamai or Digital Island hosting the content close to the
users.
>
I guess what it really boils down to is: thousands of pictures or millions
of pictures? hi res, low res, thumbnails, etc.?
>

Thousands or millions for me personally, I wouldn't attempt to manage
what databases were designed to do for me.

-- Bill
>
>
"Jerry Stuckle" <jstucklex@attg lobal.netwrote in message
news:w6OdnbneO9 eyGyTYnZ2dnUVZ_ uKknZ2d@comcast .com...

>Mikee Freedom wrote:

>>Good Morning all,
>>>
>>New member to the list, hoping you might be able to give me some much
>>needed advice.
>>>
>>Basically, I have a client who would like to offer the ability for his
>>users to have their own independent website at his domain. It is not as
>>clear cut as that but as a generic description it will do.
>>>
>>I know such services exist and I'm by no means emulating there's in any
>>way. the specific purpose of the individual user sites is fairly
>>specific, hence why he needs to get us to create it for him.
>>>
>>In a nutshell, people will be able to sign up, make some configuration
>>decisions, add some content, and have a website of their own that they
>>will be able to upload photo's to. Lot's of photo's.
>>>
>>The decision I was looking at making, was whether or not to create
>>individual databases for each of the new users. If this was going to be
>>a good idea or bad, or if it was dependent a little on further factors.
>>>
>>I've only begun to plan the site but this idea popped in to my head and
>>I was hoping someone could either say - "you ass, what are you
>>thinking?"; or indicate it may be beneficial.
>>>
>>My alternate option is to relate all content, photo's, albums, etc to
>>individual users. This is cool I guess, but liked the idea of complete
>>seperation.
>>>
>>One specific question I had was, if I needed to search for a particular
>>value in multiple databases is this going to be a pain in the ass, a
>>terrible load on the server... or anything else that I may be
>>overlooking .
>>>
>>Conclusion :
>>>
>>I like the idea of it, is it a good one?
>>Are there considerations?
>>>
>>Thanks everyone,
>>Mikee
>>>
>>p.s. if any of what I've written doesn't make sense please feel free to
>>berate or ask for further explanation :)
>>>

>One thing to consider here - the users. They'll be uploading their own
>content. Does this include server-side scripts like PHP, Perl, etc.? Will
>they need to create their own tables for anything? Will different users
>have vastly different requirements?
>>
>If so, I think you should go with separate databases for each user for
>security purposes. Give each user their own userid and password and only
>allow them access to their own database.
>>
>As for storing pictures in the database - I do it regularly. MySQL
>handles it quite well. I use mainly the InnoDB engine, so I also have
>foreign key restraints, which I set up to not allow a picture to be
>deleted as long as it's still being referenced. It also makes it easier
>to reference the pictures - you don't need a filename. I just keep the
>pictures in their own table for performance reasons and don't worry about
>it any more.
>>
>Not to mention making backups easier.
>>

I agree with Jerry completely. Although I use MyISAM tables normally.
I'm not a wizard at databases things at all. And Jerry hit on one point
that I think alot of people miss... keeping the images/binary data in
separate tables from the other data... very, very important. Using a
blob type field in any table definition automatically (and silently
mostly) converts all other fixed length fields to variable length fields
(at least in MySQL 4.?). ie: CHARS become VARCHARS.

Norm

[Jerry Stuckle]

Re: Database structure decision

AlterEgo wrote:

>
"Jerry Stuckle" <jstucklex@attg lobal.netwrote in message
news:w6OdnbneO9 eyGyTYnZ2dnUVZ_ uKknZ2d@comcast .com...

>Mikee Freedom wrote:

>>Good Morning all,
>>>
>>New member to the list, hoping you might be able to give me some much
>>needed advice.
>>>
>>Basically, I have a client who would like to offer the ability for his
>>users to have their own independent website at his domain. It is not as
>>clear cut as that but as a generic description it will do.
>>>
>>I know such services exist and I'm by no means emulating there's in any
>>way. the specific purpose of the individual user sites is fairly
>>specific, hence why he needs to get us to create it for him.
>>>
>>In a nutshell, people will be able to sign up, make some configuration
>>decisions, add some content, and have a website of their own that they
>>will be able to upload photo's to. Lot's of photo's.
>>>
>>The decision I was looking at making, was whether or not to create
>>individual databases for each of the new users. If this was going to be
>>a good idea or bad, or if it was dependent a little on further factors.
>>>
>>I've only begun to plan the site but this idea popped in to my head and
>>I was hoping someone could either say - "you ass, what are you
>>thinking?"; or indicate it may be beneficial.
>>>
>>My alternate option is to relate all content, photo's, albums, etc to
>>individual users. This is cool I guess, but liked the idea of complete
>>seperation.
>>>
>>One specific question I had was, if I needed to search for a particular
>>value in multiple databases is this going to be a pain in the ass, a
>>terrible load on the server... or anything else that I may be
>>overlooking .
>>>
>>Conclusion :
>>>
>>I like the idea of it, is it a good one?
>>Are there considerations?
>>>
>>Thanks everyone,
>>Mikee
>>>
>>p.s. if any of what I've written doesn't make sense please feel free to
>>berate or ask for further explanation :)
>>>

>One thing to consider here - the users. They'll be uploading their own
>content. Does this include server-side scripts like PHP, Perl, etc.? Will
>they need to create their own tables for anything? Will different users
>have vastly different requirements?
>>
>If so, I think you should go with separate databases for each user for
>security purposes. Give each user their own userid and password and only
>allow them access to their own database.
>>
>As for storing pictures in the database - I do it regularly. MySQL
>handles it quite well. I use mainly the InnoDB engine, so I also have
>foreign key restraints, which I set up to not allow a picture to be
>deleted as long as it's still being referenced. It also makes it easier
>to reference the pictures - you don't need a filename. I just keep the
>pictures in their own table for performance reasons and don't worry about
>it any more.
>>
>Not to mention making backups easier.
>>
>--
>============== ====
>Remove the "x" from my email address
>Jerry Stuckle
>JDS Computer Training Corp.
>jstucklex@attgl obal.net
>============== ====

>
>

(Top posting fixed)

Jerry,
>
Regarding storing images in the database:
>
1. If one is looking for quick and easy (as in a hobby application),

then I

totally agree - store them in the database. If one needs to keep a

scalable

product life-cycle in mind, then I would not keep them in the database.

I disagree. It scales quite well to larger databases. I've had
databases over in the tens of gigabytes containing pictures, PDF's and
other binary data. It works great.

2. If this is a commercial or community driven venture, then it will

have to

scale if it is successful. If it is not successful, then it really won't
matter.

The busiest has upwards of 100K hits per day average Peaks have been
over 250K. During testing we pushed it at 1M hits/day. That's well
beyond a "hobby site". In fact I wish some of my other sites got this
much traffic :-)

3. Transferring binary data from the native file system is way faster

than

any SQL database.

I suggest you check your figures. It may be a little faster - but in no
way is it "way faster".

4. File systems are more easily scaled than databases.

Again I disagree. I've been doing RDB work since the early 80's when I
started with DB2 on IBM mainframes. If properly designed, databases can
scale much better than file systems.

5. Automated image management utilities (for creating thumbnails,

converting

image formats, reading image meta-data, etc.) love working with file
systems, but hate working with databases.

So don't use them. Not a problem.

I do use them. When a new image is uploaded, for instance, I may store
a thumbnail as well as the image itself. But I don't need it after that.

And why should I waste CPU and other system resources creating
thumbnails every time they are requested?

6. Its far easier to distribute images to the "edge of the web" with
companies like Akamai or Digital Island hosting the content close to the
users.
>

That's one way to do it. But it also creates nightmare backups and the
like. Mu customers use mostly dedicated servers and VPS's.

I guess what it really boils down to is: thousands of pictures or

millions

of pictures? hi res, low res, thumbnails, etc.?
>
-- Bill
>

Tens of thousands of pictures. Hi res and thumbnails, mostly. As I
said, database size in the tens of GB. Don't know what it is lately - I
haven't looked at the size.

I suggest you try it before you start telling me how bad it is. As I
said - I've done it for a number of sites. It works great. And I've
been doing it with RDB's for a lot longer than most people in this
group. Proper design, tuning and implementation and it works quite well.

How many have you actually done this on? Or are you just talking
through your hat?

P.S. Please don't top post.






--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: Database structure decision

Gary L. Burnore wrote:

>>

>>Jerry,
>>>
>>Regarding storing images in the database:
>>>
>>1. If one is looking for quick and easy (as in a hobby application),

>then I

>>totally agree - store them in the database. If one needs to keep a

>scalable

>>product life-cycle in mind, then I would not keep them in the database.

>I disagree. It scales quite well to larger databases. I've had
>databases over in the tens of gigabytes containing pictures, PDF's and
>other binary data. It works great.

>
Wow, tens of gigabytes? Heh.
>

Yep. How many databases of that size do you deal with? From your other
statements I suspect you haven't gotten over 50Kb.

>>2. If this is a commercial or community driven venture, then it will

>have to

>>scale if it is successful. If it is not successful, then it really won't
>>matter.

>The busiest has upwards of 100K hits per day average Peaks have been
>over 250K. During testing we pushed it at 1M hits/day. That's well
>beyond a "hobby site". In fact I wish some of my other sites got this
>much traffic :-)
>>
>>

>>3. Transferring binary data from the native file system is way faster

>than

>>any SQL database.

>I suggest you check your figures. It may be a little faster - but in no
>way is it "way faster".

>
Its dependent on the filesystem, the databsae and many other things.
You're both blowing hot air.
>

Wrong. I don't know of any filesystem which can handle 100K files in
one directory very well. But 100M rows is easily handled by a good
database.

You really should get some facts before you start accusing others of
blowing hot air.

>>4. File systems are more easily scaled than databases.

>Again I disagree. I've been doing RDB work since the early 80's when I
>started with DB2 on IBM mainframes. If properly designed, databases can
>scale much better than file systems.

>
Two more moronic statements. (His and yours). Either can scale well
if designed correctly.
>

Let's see you scale a filesystem to handle 100K files in a single
directory. And no, I'm not talking about putting them in separate
directories - where the program has to decide which directory(ies) to
search for the file. I'm talking about like you do in a database - with
everything in a single table.

>>5. Automated image management utilities (for creating thumbnails,

>converting

>>image formats, reading image meta-data, etc.) love working with file
>>systems, but hate working with databases.

>So don't use them. Not a problem.
>>
>I do use them. When a new image is uploaded, for instance, I may store
>a thumbnail as well as the image itself. But I don't need it after that.
>>
>And why should I waste CPU and other system resources creating
>thumbnails every time they are requested?
>>

>>6. Its far easier to distribute images to the "edge of the web" with
>>companies like Akamai or Digital Island hosting the content close to the
>>users.
>>>

>That's one way to do it. But it also creates nightmare backups and the
>like. Mu customers use mostly dedicated servers and VPS's.

>
So backing up a bunch of dedicated servers is better how, exactly?

>>I guess what it really boils down to is: thousands of pictures or

>millions

>>of pictures? hi res, low res, thumbnails, etc.?
>>>
>>-- Bill
>>>

>Tens of thousands of pictures. Hi res and thumbnails, mostly. As I
>said, database size in the tens of GB. Don't know what it is lately - I
>haven't looked at the size.

>
When you get to tens of terabytes, then you can talk about how well
you scale. Tens of gigs or even a couple hundred is nothing anymore.
>

No, but it's bigger than most of the websites out there.

And how many filesystems handle 10's of terabytes in a single directory?

He's right about one thing. It makes far more sense to NOT store
images in a database table.

>I suggest you try it before you start telling me how bad it is. As I
>said - I've done it for a number of sites. It works great. And I've
>been doing it with RDB's for a lot longer than most people in this
>group.

>
Bullshit.
>

Yep, and you're the one who's full of it. You know nothing about my
background or my experience.

Stoopid asshole.

>Proper design, tuning and implementation and it works quite well.

>
Now that is true.
>

>How many have you actually done this on? Or are you just talking
>through your hat?

>
Better than out your ass.

>P.S. Please don't top post.

>
We agree on this.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Geoff Berrow]

Re: Database structure decision

Message-ID: <b-ydnfwzS5NGISfYn Z2dnUVZ_qunnZ2d @comcast.comfro m Jerry
Stuckle contained the following:

>I suggest you try it before you start telling me how bad it is. As I
>said - I've done it for a number of sites. It works great.

Let's say I'm interested enough to try it Jerry, have you got a little
sample code I could play around with?

--
Regards,

Geoff Berrow

[Norman Peelman]

Re: Database structure decision

Geoff Berrow wrote:

Message-ID: <b-ydnfwzS5NGISfYn Z2dnUVZ_qunnZ2d @comcast.comfro m Jerry
Stuckle contained the following:
>

>I suggest you try it before you start telling me how bad it is. As I
>said - I've done it for a number of sites. It works great.

>
Let's say I'm interested enough to try it Jerry, have you got a little
sample code I could play around with?
>

Geoff,

What would you like to see? I'll tell you my system (understand this
is a specialized sysytem):

1) I create dummy data for a set number of pictures upon account creation.
2) I pad all my images with zero bytes to a predetermined size (for me
it's 10k). This allows me to calculate the size of my expanding db at
anytime with great accurracy.
3) Prevents fragmentation when my users want to delete or replace an
existing image (as rows are not deleted and created after account
creation). I do only UPDATES (a DELETE just gets copied over with the
dummy data).
4) I pull my images out with "SELECT SUBSTRING(data, 1,$size) FROM data
WHERE id = $id LIMIT 1"; $size being the original unpadded size.
5) I then create a GD image with imagecreatefrom string(), perform
anything I want to on the image, then send it out.

I think it's a good example, again highly specialized, but it works
great. Even excluding 1,2,3,4 once you create the image (5) your
possibilities are endless. I see so many people actually write the image
data back out to the filesystem just to read it back into the script for
processing. There is no need. It can probably be done with the magicwand
api as well but i've yet to try it.

I chose this way of doing things for my needs. I'm not looking for
an ultra-compact dataset. I'm looking for ultra-ease of managability.

Norm

[Jerry Stuckle]

Re: Database structure decision

Geoff Berrow wrote:

Message-ID: <b-ydnfwzS5NGISfYn Z2dnUVZ_qunnZ2d @comcast.comfro m Jerry
Stuckle contained the following:
>

>I suggest you try it before you start telling me how bad it is. As I
>said - I've done it for a number of sites. It works great.

>
Let's say I'm interested enough to try it Jerry, have you got a little
sample code I could play around with?
>

Geoff,

No little pieces of code - it's pretty well integrated into the systems,
and I'm not on that computer today. But some ideas from memory follow.

But basically, I keep the picture itself in a separate table, containing
only the picture and an id column. This increases performance, because
most of the time you don't want the picture itself. And keeping it in a
separate table means the database doesn't have to skip over the data
when gathering the rest of the info.

The main table has things like the picture id, caption, size, and
anything else I want about the picture. These are the things you would
want/need in the HTML itself.

The picture is loaded via a standard <img... tag, pointing at a short
PHP script. For instance, to display a thumbnail for product "$prodid",
you could have something like:

$result=mysql_q uery("SELECT pixid,height,wi dth,alt from Pix WHERE " .
"prodid=$pr odid AND thumbnail=1");
if ($result) {
if (mysql_numrows == 1) {
$list($pixid, $height, $width, $alt)= mysql_fetch_arr ay($result);
echo "<img src=\"pix.php?p ixid=$pixid\" height=$height " .
"width=$wid th alt=\"$alt\" border=0>"
}
mysql_free_resu lt($result);
}

height and width contain the picture size in pixels, pixid is the id of
the picture, alt is the alt text, and thumbnail is either 1 or 0,
depending on whether it's a thumbnail (1) or not. Primary key is on
pixid and thumbnail (this would have to change if you have more than 1
picture per product).

The PHP script called in the img statement is similar to: which just
gets the picture, sends the header and then echoes the picture data.
Something like:

<?php
if (!isset($_GET(' pixid') !! trim($_GET('pix id') == "")
exit(0);

$pixid = $_GET('pixid');

$conn=mysql_con nect('localhost ', $user, $pw);
if ($conn) // Can't connect
exit(0);

$result = mysql_fetch("SE LECT ptype, picture from pix WHERE id=$pixid");
if (!$result)
exit(0);

if (mysql_num_rows ($result) != 1)
exit(0);

$data = mysql_fetch_arr ay($result);

header("Content-type: {data['ptype']");
echo $data['picture'];

msyql_free_resu lt($result);
mysql_close($co nn);

?>

Code for inserting into the database is basically similar to uploading,
except you move it from the temp directory to a work directory
(temporary name), read it and store it in the database, then delete it.

Hope this helps. It really isn't too bad.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Mikee Freedom]

Re: Database structure decision

hey again,

Thanks for your advice on this one. Given me some sound info to mull
over.

Geoff, you're probably right. Will think on it some more. And next
time I will put more thought in to the exact language I use to ask
questions RE "I like the idea". Any tips on the use of apostrophe
would be much appreciated.

AlterEgo (and others), I think I will go with the filesystem solution
on this one.

Jerry, different users won't be uploading their own scripts nor
creating tables. They will have various configuration options they can
manage for their own area. The client would like these sites to be as
seperate as possible which was my original motivation to go for
seperate databases.

Anyway, thanks again all for your help on this one. And for the info
on storing images in the DB.

cheers,
mikee

On Jan 27, 4:29 pm, Jerry Stuckle <jstuck...@attg lobal.netwrote:

Gary L. Burnore wrote:
>

>Jerry,

>

>Regarding storing images in the database:

>

>1. If one is looking for quick and easy (as in a hobby application),
then I
>totally agree - store them in the database. If one needs to keep a
scalable
>product life-cycle in mind, then I would not keep them in the database.
I disagree. It scales quite well to larger databases. I've had
databases over in the tens of gigabytes containing pictures, PDF's and
other binary data. It works great.

>

Wow, tens of gigabytes? Heh.Yep. How many databases of that size do you deal with? From your other

statements I suspect you haven't gotten over 50Kb.
>
>
>

>2. If this is a commercial or community driven venture, then it will
have to
>scale if it is successful. If it is not successful, then it really won't
>matter.
The busiest has upwards of 100K hits per day average Peaks have been
over 250K. During testing we pushed it at 1M hits/day. That's well
beyond a "hobby site". In fact I wish some of my other sites got this
much traffic :-)

>

>3. Transferring binary data from the native file system is way faster
than
>any SQL database.
I suggest you check your figures. It may be a little faster - but in no
way is it "way faster".

>

Its dependent on the filesystem, the databsae and many other things.
You're both blowing hot air.Wrong. I don't know of any filesystem which can handle 100K files in

one directory very well. But 100M rows is easily handled by a good
database.
>
You really should get some facts before you start accusing others of
blowing hot air.
>

>4. File systems are more easily scaled than databases.
Again I disagree. I've been doing RDB work since the early 80's when I
started with DB2 on IBM mainframes. If properly designed, databases can
scale much better than file systems.

>

Two more moronic statements. (His and yours). Either can scale well
if designed correctly. Let's see you scale a filesystem to handle 100K files in a single

directory. And no, I'm not talking about putting them in separate
directories - where the program has to decide which directory(ies) to
search for the file. I'm talking about like you do in a database - with
everything in a single table.
>
>
>

>5. Automated image management utilities (for creating thumbnails,
converting
>image formats, reading image meta-data, etc.) love working with file
>systems, but hate working with databases.
So don't use them. Not a problem.

>

I do use them. When a new image is uploaded, for instance, I may store
a thumbnail as well as the image itself. But I don't need it after that.

>

And why should I waste CPU and other system resources creating
thumbnails every time they are requested?

>

>6. Its far easier to distribute images to the "edge of the web" with
>companies like Akamai or Digital Island hosting the content close to the
>users.

>

That's one way to do it. But it also creates nightmare backups and the
like. Mu customers use mostly dedicated servers and VPS's.

>

So backing up a bunch of dedicated servers is better how, exactly?

>I guess what it really boils down to is: thousands of pictures or
millions
>of pictures? hi res, low res, thumbnails, etc.?

>

>-- Bill

>

Tens of thousands of pictures. Hi res and thumbnails, mostly. As I
said, database size in the tens of GB. Don't know what it is lately - I
haven't looked at the size.

>

When you get to tens of terabytes, then you can talk about how well
you scale. Tens of gigs or even a couple hundred is nothing anymore.No, but it's bigger than most of the websites out there.

>
And how many filesystems handle 10's of terabytes in a single directory?
>

He's right about one thing. It makes far more sense to NOT store
images in a database table.

I suggest you try it before you start telling me how bad it is. As I
said - I've done it for a number of sites. It works great. And I've
been doing it with RDB's for a lot longer than most people in this
group.

>

Bullshit.Yep, and you're the one who's full of it. You know nothing about my

background or my experience.
>
Stoopid asshole.
>

Proper design, tuning and implementation and it works quite well.

>

Now that is true.

>

How many have you actually done this on? Or are you just talking
through your hat?

>

Better than out your ass.

P.S. Please don't top post.

>

We agree on this.--

=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@attgl obal.net
=============== ===

[Geoff Berrow]

Re: Database structure decision

Message-ID: <1170111287.604 103.46780@k78g2 000cwa.googlegr oups.comfrom
Mikee Freedom contained the following:

>Geoff, you're probably right. Will think on it some more. And next
>time I will put more thought in to the exact language I use to ask
>questions RE "I like the idea". Any tips on the use of apostrophe
>would be much appreciated.

http://www.apostrophe.fsnet.co.uk/

and if that's not enough there is even a newsgroup

alt.possessive. its.has.no.apos trophe

--
Regards,

Geoff Berrow

[AlterEgo]

Re: Database structure decision

Jerry,

Chill dude. I don't want to play techie egos here, but its late afternoon, I
need a little R&R, and since I was asked:

Currently, Director of Emerging Technologies

https://www.connect3.com

Our systems manage product content, pricing and advertising (print and web)
production for large retailers. - over a terabyte of millions of high and
low res images combined.
Home Depot, Best Buy, Circuit City, Petco ...

Before that:
V.P. Technology

https://www.local.com

Processing 170 million search requests/day, 1.2 Billion click-steram
transactions/day across four data centers - all transactions in distributed
relational databases.

Speaking engagements:
Professional Association for SQL Server: Enterprise Class Service Levels on
a Dotcom Budget.
L.A. .NET Users Group: Breaking the Rules for Blistering OLTP Performance.
.... others.

Amazon, eBay, Akamai and Google do not store theire images in a database. As
a matter of fact, Google uses a file system to index *all* of its data:
http://216.239.37.132/papers/gfs-sosp2003.pdf. Are they wrong also?

Also in my posting I said to store the images in a hive folder structure
(unbalanced tree), not in one directory - jeez! If you choose to use UUID's
(GUIDs) as filenames, you get a remakably balanced tree - at least up
through the first 12 characters.

If scaling isn't an issue, by all means store images in a database. If it is
an issue, then I'll side with the big boys and store them in a file system.
They know a little bit about implementing technology.

Top poster and always will be, sorry.

-- Bill

"Jerry Stuckle" <jstucklex@attg lobal.netwrote in message
news:b-ydnfwzS5NGISfYn Z2dnUVZ_qunnZ2d @comcast.com...

AlterEgo wrote:

>>
>"Jerry Stuckle" <jstucklex@attg lobal.netwrote in message
>news:w6OdnbneO 9eyGyTYnZ2dnUVZ _uKknZ2d@comcas t.com...

>>Mikee Freedom wrote:
>>>Good Morning all,
>>>>
>>>New member to the list, hoping you might be able to give me some much
>>>needed advice.
>>>>
>>>Basically, I have a client who would like to offer the ability for his
>>>users to have their own independent website at his domain. It is not as
>>>clear cut as that but as a generic description it will do.
>>>>
>>>I know such services exist and I'm by no means emulating there's in any
>>>way. the specific purpose of the individual user sites is fairly
>>>specific, hence why he needs to get us to create it for him.
>>>>
>>>In a nutshell, people will be able to sign up, make some configuration
>>>decisions, add some content, and have a website of their own that they
>>>will be able to upload photo's to. Lot's of photo's.
>>>>
>>>The decision I was looking at making, was whether or not to create
>>>individual databases for each of the new users. If this was going to be
>>>a good idea or bad, or if it was dependent a little on further factors.
>>>>
>>>I've only begun to plan the site but this idea popped in to my head and
>>>I was hoping someone could either say - "you ass, what are you
>>>thinking?" ; or indicate it may be beneficial.
>>>>
>>>My alternate option is to relate all content, photo's, albums, etc to
>>>individual users. This is cool I guess, but liked the idea of complete
>>>seperation .
>>>>
>>>One specific question I had was, if I needed to search for a particular
>>>value in multiple databases is this going to be a pain in the ass, a
>>>terrible load on the server... or anything else that I may be
>>>overlookin g.
>>>>
>>>Conclusion :
>>>>
>>>I like the idea of it, is it a good one?
>>>Are there considerations?
>>>>
>>>Thanks everyone,
>>>Mikee
>>>>
>>>p.s. if any of what I've written doesn't make sense please feel free to
>>>berate or ask for further explanation :)
>>>>
>>One thing to consider here - the users. They'll be uploading their own
>>content. Does this include server-side scripts like PHP, Perl, etc.?
>>Will they need to create their own tables for anything? Will different
>>users have vastly different requirements?
>>>
>>If so, I think you should go with separate databases for each user for
>>security purposes. Give each user their own userid and password and
>>only allow them access to their own database.
>>>
>>As for storing pictures in the database - I do it regularly. MySQL
>>handles it quite well. I use mainly the InnoDB engine, so I also have
>>foreign key restraints, which I set up to not allow a picture to be
>>deleted as long as it's still being referenced. It also makes it easier
>>to reference the pictures - you don't need a filename. I just keep the
>>pictures in their own table for performance reasons and don't worry
>>about it any more.
>>>
>>Not to mention making backups easier.
>>>
>>--
>>============= =====
>>Remove the "x" from my email address
>>Jerry Stuckle
>>JDS Computer Training Corp.
>>jstucklex@attgl obal.net
>>============= =====

>>

>
(Top posting fixed)
>

Jerry,

Regarding storing images in the database:

1. If one is looking for quick and easy (as in a hobby application),

then I

totally agree - store them in the database. If one needs to keep a

scalable

product life-cycle in mind, then I would not keep them in the database.

>
I disagree. It scales quite well to larger databases. I've had databases
over in the tens of gigabytes containing pictures, PDF's and other binary
data. It works great.
>

2. If this is a commercial or community driven venture, then it will

have to

scale if it is successful. If it is not successful, then it really won't
matter.

>
The busiest has upwards of 100K hits per day average Peaks have been over
250K. During testing we pushed it at 1M hits/day. That's well beyond a
"hobby site". In fact I wish some of my other sites got this much traffic
:-)
>
>

3. Transferring binary data from the native file system is way faster

than

any SQL database.

>
I suggest you check your figures. It may be a little faster - but in no
way is it "way faster".
>

4. File systems are more easily scaled than databases.

>
Again I disagree. I've been doing RDB work since the early 80's when I
started with DB2 on IBM mainframes. If properly designed, databases can
scale much better than file systems.
>

5. Automated image management utilities (for creating thumbnails,

converting

image formats, reading image meta-data, etc.) love working with file
systems, but hate working with databases.

>
So don't use them. Not a problem.
>
I do use them. When a new image is uploaded, for instance, I may store a
thumbnail as well as the image itself. But I don't need it after that.
>
And why should I waste CPU and other system resources creating thumbnails
every time they are requested?
>

6. Its far easier to distribute images to the "edge of the web" with
companies like Akamai or Digital Island hosting the content close to the
users.

That's one way to do it. But it also creates nightmare backups and the
like. Mu customers use mostly dedicated servers and VPS's.
>

I guess what it really boils down to is: thousands of pictures or

millions

of pictures? hi res, low res, thumbnails, etc.?

-- Bill

>
Tens of thousands of pictures. Hi res and thumbnails, mostly. As I said,
database size in the tens of GB. Don't know what it is lately - I haven't
looked at the size.
>
I suggest you try it before you start telling me how bad it is. As I
said - I've done it for a number of sites. It works great. And I've been
doing it with RDB's for a lot longer than most people in this group.
Proper design, tuning and implementation and it works quite well.
>
How many have you actually done this on? Or are you just talking through
your hat?
>
P.S. Please don't top post.
>
>
>
>
>
>
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: Database structure decision

AlterEgo wrote:

>
Also in my posting I said to store the images in a hive folder structure
(unbalanced tree), not in one directory - jeez! If you choose to use UUID's
(GUIDs) as filenames, you get a remakably balanced tree - at least up
through the first 12 characters.
>

Yep, and as you add new directories you need to keep changing the code.
And it creates a management nightmare. What happens when you want to
delete an image? Is it used by anything, for instance?

If scaling isn't an issue, by all means store images in a database. If it is
an issue, then I'll side with the big boys and store them in a file system.
They know a little bit about implementing technology.
>

The "big boys" do store images in databases. We used to do it all the
way back in the 80's on mainframes - for instance, scanned documents.
And we did it for big companies (I was working for IBM at the time). It
scales quite well.

Don't tell me it doesn't scale when you haven't tried it. I have. And
it does - quite well.

Top poster and always will be, sorry.
>
-- Bill

That says it all.
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===