BEST SOLUTION FOR TAGS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

BEST SOLUTION FOR TAGS

Author

Topic

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-07-16 : 11:06:55

Dear All,

I need to create a table that contains path of image file name and their relavant tags(i.e related to nature, people, wallpaper etc). User will input their values with space separated. Can anyone please give me the best idea to design the table so that i can easily call the images with tags in my asp pages.

Thanks in advance.

____________
Praba

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 11:10:12

Use CROSS APPLY if using SQL Server 2005. If not using SQL Server 2005, use a scalar valued function.

E 12°55'05.25"
N 56°04'39.16"

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-07-16 : 11:22:18

I am very new to sql server, can you please paste the sample table with design view.

I am using sql server 8.

____________
Praba

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-16 : 11:33:02

You need 3 tables:

A table of TAGS
A table of IMAGES
And a table that relates TAGS to IMAGES

Something like this:

TAGS (TagName)
IMAGES (ImageID, ImagePath, Description, etc ...)
IMAGETAGS (ImageID, TagName)

- Jeff
http://weblogs.sqlteam.com/JeffS

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-07-16 : 11:46:34

So the IMAGETAGS table contains (ImageID, TagName)

Tagid Tagname
1 nature wallpaper photoshop
2 nature flash photoshop
3 nature sqlserver photoshop
4 nature personal photoshop

Later i will call with like command?

____________
Praba

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-07-16 : 11:47:28

If the user click the "photoshop" it will fetch the images with tags "photoshop".

____________
Praba

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-16 : 12:06:19

quote:
Originally posted by cutepraba

So the IMAGETAGS table contains (ImageID, TagName)

Tagid Tagname
1 nature wallpaper photoshop
2 nature flash photoshop
3 nature sqlserver photoshop
4 nature personal photoshop

Later i will call with like command?

____________
Praba

No. Each row in the tag table should be a single tag. That is, the list should be:

nature
flash
sqlserver
personal
photoshop
..etc ...

Right? each tag row = one single tag. Then, if picture #2 is tagged with "personal" and "photoshop", then your IMAGETAGS table would be:

PictureID, TagName
2,personal
2,photoshop

Notice that pictureID #2 is associated with 2 tags, one per row. You can add/remove tags for this picture easily by adding or removing rows from this table.

A single column in a single row should never have more than 1 piece of information on it. By designing your system this way, you can have as many tags as you want,and each image can have as many tags as you want.

- Jeff
http://weblogs.sqlteam.com/JeffS

cutepraba
Yak Posting Veteran

53 Posts

Posted - 2008-07-16 : 12:29:29

Which way is fastest.
each tag row = one single tag
multiple tags in one singel field?
Because i will use my tables in asp pages.

____________
Praba

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-16 : 14:06:14

I've already told you.

More on this here:

http://www.datamodel.org/NormalizationRules.html
http://en.wikipedia.org/wiki/Database_normalization

You should try to learn the basics of normalization and database design.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources