Design tags functionality

  • Thread starter Thread starter Tem
  • Start date Start date
T

Tem

Hello

I need to design a photo gallery, each photo can be assigned tags to just
like in flickr.
What would be the most flexible and efficient design for a tagging system.

Right now I have a photos table
ID, PhotoName, Url, Tags
1, cat, http://something.com/cat.jpg, cat animal ..

Should I have additional tables for just for the tags..?

Thank you in advance
Tem
 
The current design works but I think it would be a lot better to have a
[PhotoTag] table that holds ptId, PhotoId, and Tag. Keep in mind that it will
only be beneficial if you will have each tag in a separate row within the
PhotoTag table.

The immediate benefit for doing this is that it would allow you to do more
efficient group by queries against the tags. The other benefit that this will
afford you is that you can have a lot of flexibility and customization to
tags like giving tags weights.

For example, you could add a column PhotoTag.weight. The weight column will
hold a number from 1 to 100 or a number from 0.00 - 1.00. For each photo
added you will add a record in the Photos table and another one in the
PhotoTag table. The PhotoTag.Tag will be the same as Photos.Name and the
weight for example will be 50. When you add other tags later you can add each
tag to PhotoTag with a weight of only 0.5. This gives more weight to the name
of the photo over other tags. Suppose the name was Cat and you added another
2 tags one called 'Grey Cat' and the third is 'Grey'. Suppose there was
another photo called 'Timmy' and another tag called 'Timmy holding Cat'. Your
PhotoTag table will look like this

ptId, PhotoId, Tag, Weight
1, 1234, Cat, 50
2, 1234, Grey Cat, 0.5
3, 1234, Grey, 0.5
4, 2345, Timmy, 50
5, 2345, Timmy Holding Cat, 0.5

If you were to search for cat you can then get better relevace by running a
query like this

Select PhotoID, sum(weight) as weight from PhotoTag Where Tag like '%cat%'
group by PhotoID Order by 2 Desc

In this case you'll get 2 rows

PhotoId,weight
1234, 50.5
2345, 0.5

You can use the weight field in so many ways. You can get elaborate and
weight tags by order so that the sum of all weights for any given photo is
always 100.
so in the case above

ptId, PhotoId, Tag, Weight
1, 1234, Cat, 50
2, 1234, Grey Cat, 33.33
3, 1234, Grey, 16.66
4, 2345, Timmy, 66.66
5, 2345, Timmy Holding Cat, 33.33

If you do this the results will actually have estimated percentage of
relevance and your query would return the following

PhotoId,weight
1234, 83.33
2345, 33.33

This will definitely require more coding but the table structure allows it
without much altering.

Let me know if that convinces you to add the PhotoTag table.

Thanks,
 
Thanks for your help, it does seem to have many benefits.
But there's a problem with both my method and yours. If the words in the
tags are the same but ordered differently would result in redundant entries
and unreliable weights
I haven't been able to come up with something better.

Thanks,
Tem


Mohamad Elarabi said:
The current design works but I think it would be a lot better to have a
[PhotoTag] table that holds ptId, PhotoId, and Tag. Keep in mind that it
will
only be beneficial if you will have each tag in a separate row within the
PhotoTag table.

The immediate benefit for doing this is that it would allow you to do more
efficient group by queries against the tags. The other benefit that this
will
afford you is that you can have a lot of flexibility and customization to
tags like giving tags weights.

For example, you could add a column PhotoTag.weight. The weight column
will
hold a number from 1 to 100 or a number from 0.00 - 1.00. For each photo
added you will add a record in the Photos table and another one in the
PhotoTag table. The PhotoTag.Tag will be the same as Photos.Name and the
weight for example will be 50. When you add other tags later you can add
each
tag to PhotoTag with a weight of only 0.5. This gives more weight to the
name
of the photo over other tags. Suppose the name was Cat and you added
another
2 tags one called 'Grey Cat' and the third is 'Grey'. Suppose there was
another photo called 'Timmy' and another tag called 'Timmy holding Cat'.
Your
PhotoTag table will look like this

ptId, PhotoId, Tag, Weight
1, 1234, Cat, 50
2, 1234, Grey Cat, 0.5
3, 1234, Grey, 0.5
4, 2345, Timmy, 50
5, 2345, Timmy Holding Cat, 0.5

If you were to search for cat you can then get better relevace by running
a
query like this

Select PhotoID, sum(weight) as weight from PhotoTag Where Tag like '%cat%'
group by PhotoID Order by 2 Desc

In this case you'll get 2 rows

PhotoId,weight
1234, 50.5
2345, 0.5

You can use the weight field in so many ways. You can get elaborate and
weight tags by order so that the sum of all weights for any given photo is
always 100.
so in the case above

ptId, PhotoId, Tag, Weight
1, 1234, Cat, 50
2, 1234, Grey Cat, 33.33
3, 1234, Grey, 16.66
4, 2345, Timmy, 66.66
5, 2345, Timmy Holding Cat, 33.33

If you do this the results will actually have estimated percentage of
relevance and your query would return the following

PhotoId,weight
1234, 83.33
2345, 33.33

This will definitely require more coding but the table structure allows it
without much altering.

Let me know if that convinces you to add the PhotoTag table.

Thanks,

--
Mohamad Elarabi
MCP, MCTS, MCPD.


Tem said:
Hello

I need to design a photo gallery, each photo can be assigned tags to just
like in flickr.
What would be the most flexible and efficient design for a tagging
system.

Right now I have a photos table
ID, PhotoName, Url, Tags
1, cat, http://something.com/cat.jpg, cat animal ..

Should I have additional tables for just for the tags..?

Thank you in advance
Tem
Click to expand...
Click to expand...
 
You'll have to instruct your users to enter tags in order of importance or
relevance for better search results. You can also use the name of the picture
to apply more weight to tags that share similar words with the name etc.
There is no surefire way to come up with relevance if your users aren't
cooperative or sensitive to this fact.
In any case whether you apply some AI or a combination of AI and your users'
cooperation, a separate table isn't such a bad idea to start with.

Best of luck,

--
Mohamad Elarabi
MCP, MCTS, MCPD.


Tem said:
Thanks for your help, it does seem to have many benefits.
But there's a problem with both my method and yours. If the words in the
tags are the same but ordered differently would result in redundant entries
and unreliable weights
I haven't been able to come up with something better.

Thanks,
Tem


Mohamad Elarabi said:
The current design works but I think it would be a lot better to have a
[PhotoTag] table that holds ptId, PhotoId, and Tag. Keep in mind that it
will
only be beneficial if you will have each tag in a separate row within the
PhotoTag table.

The immediate benefit for doing this is that it would allow you to do more
efficient group by queries against the tags. The other benefit that this
will
afford you is that you can have a lot of flexibility and customization to
tags like giving tags weights.

For example, you could add a column PhotoTag.weight. The weight column
will
hold a number from 1 to 100 or a number from 0.00 - 1.00. For each photo
added you will add a record in the Photos table and another one in the
PhotoTag table. The PhotoTag.Tag will be the same as Photos.Name and the
weight for example will be 50. When you add other tags later you can add
each
tag to PhotoTag with a weight of only 0.5. This gives more weight to the
name
of the photo over other tags. Suppose the name was Cat and you added
another
2 tags one called 'Grey Cat' and the third is 'Grey'. Suppose there was
another photo called 'Timmy' and another tag called 'Timmy holding Cat'.
Your
PhotoTag table will look like this

ptId, PhotoId, Tag, Weight
1, 1234, Cat, 50
2, 1234, Grey Cat, 0.5
3, 1234, Grey, 0.5
4, 2345, Timmy, 50
5, 2345, Timmy Holding Cat, 0.5

If you were to search for cat you can then get better relevace by running
a
query like this

Select PhotoID, sum(weight) as weight from PhotoTag Where Tag like '%cat%'
group by PhotoID Order by 2 Desc

In this case you'll get 2 rows

PhotoId,weight
1234, 50.5
2345, 0.5

You can use the weight field in so many ways. You can get elaborate and
weight tags by order so that the sum of all weights for any given photo is
always 100.
so in the case above

ptId, PhotoId, Tag, Weight
1, 1234, Cat, 50
2, 1234, Grey Cat, 33.33
3, 1234, Grey, 16.66
4, 2345, Timmy, 66.66
5, 2345, Timmy Holding Cat, 33.33

If you do this the results will actually have estimated percentage of
relevance and your query would return the following

PhotoId,weight
1234, 83.33
2345, 33.33

This will definitely require more coding but the table structure allows it
without much altering.

Let me know if that convinces you to add the PhotoTag table.

Thanks,

--
Mohamad Elarabi
MCP, MCTS, MCPD.


Tem said:
Hello

I need to design a photo gallery, each photo can be assigned tags to just
like in flickr.
What would be the most flexible and efficient design for a tagging
system.

Right now I have a photos table
ID, PhotoName, Url, Tags
1, cat, http://something.com/cat.jpg, cat animal ..

Should I have additional tables for just for the tags..?

Thank you in advance
Tem
Click to expand...
Click to expand...
Click to expand...
 
I found this
http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html

seems like the 3 table method is the most flexible design

what do you think?

Mohamad Elarabi said:
You'll have to instruct your users to enter tags in order of importance or
relevance for better search results. You can also use the name of the
picture
to apply more weight to tags that share similar words with the name etc.
There is no surefire way to come up with relevance if your users aren't
cooperative or sensitive to this fact.
In any case whether you apply some AI or a combination of AI and your
users'
cooperation, a separate table isn't such a bad idea to start with.

Best of luck,

--
Mohamad Elarabi
MCP, MCTS, MCPD.


Tem said:
Thanks for your help, it does seem to have many benefits.
But there's a problem with both my method and yours. If the words in the
tags are the same but ordered differently would result in redundant
entries
and unreliable weights
I haven't been able to come up with something better.

Thanks,
Tem


"Mohamad Elarabi [MCPD]" <[email protected]>
wrote in message
The current design works but I think it would be a lot better to have a
[PhotoTag] table that holds ptId, PhotoId, and Tag. Keep in mind that
it
will
only be beneficial if you will have each tag in a separate row within
the
PhotoTag table.

The immediate benefit for doing this is that it would allow you to do
more
efficient group by queries against the tags. The other benefit that
this
will
afford you is that you can have a lot of flexibility and customization
to
tags like giving tags weights.

For example, you could add a column PhotoTag.weight. The weight column
will
hold a number from 1 to 100 or a number from 0.00 - 1.00. For each
photo
added you will add a record in the Photos table and another one in the
PhotoTag table. The PhotoTag.Tag will be the same as Photos.Name and
the
weight for example will be 50. When you add other tags later you can
add
each
tag to PhotoTag with a weight of only 0.5. This gives more weight to
the
name
of the photo over other tags. Suppose the name was Cat and you added
another
2 tags one called 'Grey Cat' and the third is 'Grey'. Suppose there was
another photo called 'Timmy' and another tag called 'Timmy holding
Cat'.
Your
PhotoTag table will look like this

ptId, PhotoId, Tag, Weight
1, 1234, Cat, 50
2, 1234, Grey Cat, 0.5
3, 1234, Grey, 0.5
4, 2345, Timmy, 50
5, 2345, Timmy Holding Cat, 0.5

If you were to search for cat you can then get better relevace by
running
a
query like this

Select PhotoID, sum(weight) as weight from PhotoTag Where Tag like
'%cat%'
group by PhotoID Order by 2 Desc

In this case you'll get 2 rows

PhotoId,weight
1234, 50.5
2345, 0.5

You can use the weight field in so many ways. You can get elaborate and
weight tags by order so that the sum of all weights for any given photo
is
always 100.
so in the case above

ptId, PhotoId, Tag, Weight
1, 1234, Cat, 50
2, 1234, Grey Cat, 33.33
3, 1234, Grey, 16.66
4, 2345, Timmy, 66.66
5, 2345, Timmy Holding Cat, 33.33

If you do this the results will actually have estimated percentage of
relevance and your query would return the following

PhotoId,weight
1234, 83.33
2345, 33.33

This will definitely require more coding but the table structure allows
it
without much altering.

Let me know if that convinces you to add the PhotoTag table.

Thanks,

--
Mohamad Elarabi
MCP, MCTS, MCPD.


:

Hello

I need to design a photo gallery, each photo can be assigned tags to
just
like in flickr.
What would be the most flexible and efficient design for a tagging
system.

Right now I have a photos table
ID, PhotoName, Url, Tags
1, cat, http://something.com/cat.jpg, cat animal ..

Should I have additional tables for just for the tags..?

Thank you in advance
Tem
Click to expand...
Click to expand...
Click to expand...
 
A many to many relationship between tags and photos could make your tag
inserts kind of more difficult than normal. That's my opinion.

--
Mohamad Elarabi
MCP, MCTS, MCPD.


Tem said:
I found this
http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html

seems like the 3 table method is the most flexible design

what do you think?

Mohamad Elarabi said:
You'll have to instruct your users to enter tags in order of importance or
relevance for better search results. You can also use the name of the
picture
to apply more weight to tags that share similar words with the name etc.
There is no surefire way to come up with relevance if your users aren't
cooperative or sensitive to this fact.
In any case whether you apply some AI or a combination of AI and your
users'
cooperation, a separate table isn't such a bad idea to start with.

Best of luck,

--
Mohamad Elarabi
MCP, MCTS, MCPD.


Tem said:
Thanks for your help, it does seem to have many benefits.
But there's a problem with both my method and yours. If the words in the
tags are the same but ordered differently would result in redundant
entries
and unreliable weights
I haven't been able to come up with something better.

Thanks,
Tem


"Mohamad Elarabi [MCPD]" <[email protected]>
wrote in message
The current design works but I think it would be a lot better to have a
[PhotoTag] table that holds ptId, PhotoId, and Tag. Keep in mind that
it
will
only be beneficial if you will have each tag in a separate row within
the
PhotoTag table.

The immediate benefit for doing this is that it would allow you to do
more
efficient group by queries against the tags. The other benefit that
this
will
afford you is that you can have a lot of flexibility and customization
to
tags like giving tags weights.

For example, you could add a column PhotoTag.weight. The weight column
will
hold a number from 1 to 100 or a number from 0.00 - 1.00. For each
photo
added you will add a record in the Photos table and another one in the
PhotoTag table. The PhotoTag.Tag will be the same as Photos.Name and
the
weight for example will be 50. When you add other tags later you can
add
each
tag to PhotoTag with a weight of only 0.5. This gives more weight to
the
name
of the photo over other tags. Suppose the name was Cat and you added
another
2 tags one called 'Grey Cat' and the third is 'Grey'. Suppose there was
another photo called 'Timmy' and another tag called 'Timmy holding
Cat'.
Your
PhotoTag table will look like this

ptId, PhotoId, Tag, Weight
1, 1234, Cat, 50
2, 1234, Grey Cat, 0.5
3, 1234, Grey, 0.5
4, 2345, Timmy, 50
5, 2345, Timmy Holding Cat, 0.5

If you were to search for cat you can then get better relevace by
running
a
query like this

Select PhotoID, sum(weight) as weight from PhotoTag Where Tag like
'%cat%'
group by PhotoID Order by 2 Desc

In this case you'll get 2 rows

PhotoId,weight
1234, 50.5
2345, 0.5

You can use the weight field in so many ways. You can get elaborate and
weight tags by order so that the sum of all weights for any given photo
is
always 100.
so in the case above

ptId, PhotoId, Tag, Weight
1, 1234, Cat, 50
2, 1234, Grey Cat, 33.33
3, 1234, Grey, 16.66
4, 2345, Timmy, 66.66
5, 2345, Timmy Holding Cat, 33.33

If you do this the results will actually have estimated percentage of
relevance and your query would return the following

PhotoId,weight
1234, 83.33
2345, 33.33

This will definitely require more coding but the table structure allows
it
without much altering.

Let me know if that convinces you to add the PhotoTag table.

Thanks,

--
Mohamad Elarabi
MCP, MCTS, MCPD.


:

Hello

I need to design a photo gallery, each photo can be assigned tags to
just
like in flickr.
What would be the most flexible and efficient design for a tagging
system.

Right now I have a photos table
ID, PhotoName, Url, Tags
1, cat, http://something.com/cat.jpg, cat animal ..

Should I have additional tables for just for the tags..?

Thank you in advance
Tem
Click to expand...
Click to expand...
Click to expand...
 
Back
Top