Saving image tags?

[Philipp Lenssen]

What kind of structure would you suggest for saving user-added
keywords for images? And is there any difference in regards to MyISAM
vs InnoDB with this question? (Say I'm expecting a couple of hundred
thousand of images with a million tags with a couple of ten thousand
tags added every day or something, not sure.)

E.g. should I use a tags table with
id | image_id | tag_as_a_string | user_id
or
id | image_id | tag_as_a_string | tag_count
or more like
id | image_id | tag_id_as_integer (pointing to another table with
the strings)
or should I create a maximum amount of tags and add to the main images
table as tag1, tag2 etc.

I'm also asking 'cause I often run into speed problems, I'm using a
managed 1and1 server solution. Thanks for any help!

[Paul Lautman]

As a general rule, use InnoDB only where transactional processing is
required. It can also save a lot of SQL programming if you utilise its
foreign key support, but for most simple cascade on delete type operations,
I tend to use MyISAM and a multi-table delete statement.

Since tags will made up by the users all that is really required is:

Image table
image_id | image | image_name | image_description | owner_id

Tag table
image_id | keyword

This is assuming that the tags are supplied by the person who uploads the
images. Thus the tags belong to the image.

If tags can be supplied by any user for their own purposes, then the tags
belong to the user. This will require a different structure for the tag
table:
user_id | image_id | keyword

In this application, I don't see that anything will be gained by pulling
keywords off to a separate table.

[Philipp Lenssen]

> As a general rule, use InnoDB only where transactional processing is
> required. It can also save a lot of SQL programming if you utilise its
> foreign key support, but for most simple cascade on delete type operations,
> I tend to use MyISAM and a multi-table delete statement.

Thanks Paul for your help!

[Peter H. Coffin]

On Sat, 19 Jul 2008 10:17:51 +0100, Paul Lautman wrote:
> As a general rule, use InnoDB only where transactional processing is
> required. It can also save a lot of SQL programming if you utilise its
> foreign key support, but for most simple cascade on delete type operations,
> I tend to use MyISAM and a multi-table delete statement.

I'm glad you're happy with it. (:

I always find multi-table deletes much more of a pain than cascading.
Especially when it comes time to update the code of a large application
when you need to modify the database.

--
Kyle J Cardoza <ad...@zetachannel.com> sigged:
>Faith does not, in fact, move mountains;
Mainly because they won't let her loose with a drilling crew and enough
dynamite. -- Chris Suslowicz in the Monastery