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,
--
Mohamad Elarabi
MCP, MCTS, MCPD.
"Tem" wrote:
> 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
>
>
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]"
wrote in message news:0F413C0B-E85F-4B58-A2B1-16A0E004339D@microsoft.com...
> 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" wrote:
>
>> 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
>>
>>
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" wrote:
> 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]"
> wrote in message news:0F413C0B-E85F-4B58-A2B1-16A0E004339D@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >>
>
I found this
http://www.pui.ch/phred/archives/2005/04/tags-database-schem as.html
seems like the 3 table method is the most flexible design
what do you think?
"Mohamad Elarabi [MCPD]"
wrote in message news:41712242-CA79-48A2-AFB1-974E841E4931@microsoft.com...
> 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" wrote:
>
>> 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]"
>> wrote in message
>> news:0F413C0B-E85F-4B58-A2B1-16A0E004339D@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >>
>>
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" wrote:
> I found this
> http://www.pui.ch/phred/archives/2005/04/tags-database-schem as.html
>
> seems like the 3 table method is the most flexible design
>
> what do you think?
>
> "Mohamad Elarabi [MCPD]"
> wrote in message news:41712242-CA79-48A2-AFB1-974E841E4931@microsoft.com...
> > 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" wrote:
> >
> >> 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]"
> >> wrote in message
> >> news:0F413C0B-E85F-4B58-A2B1-16A0E004339D@microsoft.com...
> >> > 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" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >>
>