Managing image tags in SQL

Question

I would like to create an image database. Each image can have one or more tags, such as: Paris, April 2010, David.

How do you store this information? I thought the table Fileshas 1 row per file, and one of the columns is tag identifiers separated by commas, for example: 2,4,14,15

In another table called TagsI thought that I have one row per tag, for example:

Tag ID    Tag Name
------    --------
   1       April
   2       David
   3       Paris

Do you think this is a good idea for tag management? For example, how can I easily get all the tag names of a particular image?

Answer 1

3 . , FileTags.

FileTags . .

:

select distinct f.* from Files f 
join FileTags ft on f.FileID = ft.FileID 
join Tags t on ft.TagID = t.TagID
where t.TagName = 'Paris'

:

select distinct t.* from Files f 
join FileTags ft on f.FileID = ft.FileID 
join Tags t on ft.TagID = t.TagID
where f.FileID = 7

Answer 2

, ( , ), no-no , normalized.

" " TagId FileId.

, (, Paris) . .

Answer 3

, , , " ". , , "--", PhotoID TagID, , .

Answer 4

, , , "--", . , .

Answer 5

Mysql

SELECT f.FileID, group_concat(t.tag_name) 
FROM Files f 
     JOIN FileTags ft ON f.FileID = ft.FileID 
     JOIN Tags t on ft.TagID = t.TagID
GROUP BY f.FileID

.

, , .

: RDBMS ( mysql), RBMS ( ).

, , , , 64 .

Answer 6

3 :

• "Id", "",...
• "Id", "Name"...
• FileTag "FileId", "TagId",...

:

• "FileId" - .
• "TagId" .

, .