CODEWITHSUNDEEP
postgresqldatabase-design
Not an ID
Not an ID

Reputation: 2589

Table structure for tags

Say I want to store some image ids and their tags in a Postgres 9.3 DB, so that I can:

1) Given an image id, I can get its tags;

2) Given a tag, I can find those images which have this tag.

Which table structure is better for this problem?

a) image_id as PKEY, column tag stores the tags associated with this image as an array;

b) tag as PKEY, column image_id stores the image_ids who have this tag as an array;

c) (image_id, tag) as PKEY.

d) Other table structure.

I think c) is better, but I don't have any real experience, so any suggestions?

Thanks.

Upvotes: 1

Views: 309

Answers (1)

Joe Love
Joe Love

Reputation: 5932

Your option C is better primarily because it can be indexed by tag and also enforce a foreign key back to the image table.

Solution b can also be indexed by tag, but cannot have a good foreign key constraint and you'll have to run an expensive query when deleting images.

Solution a has the foreign key, but cannot be indexed by tag and will be the slowest of the solutions, depending on the specific query.

Upvotes: 1

Related Questions