Let's say you have a table called Threads, and each thread can have zero or more "tags" associated with it. A tag is just a byte which maps to some enum somewhere.
There's two ways I can think of to do this. The first would be to have:
create table Threads (
Id uuid not null,
Posted timestamp not null,
Subject varchar(255) not null,
Replies int4 not null,
PosterId uuid not null,
primary key (Id)
);
and a separate table for each tag on a thread:
create table ThreadTags (
Id uuid not null,
Tag int2 not null,
ThreadId uuid not null,
primary key (Id)
)
I can query for all threads with a certain tag using a join:
select * from Threads T
inner join ThreadTags tag ON tag.ThreadId = T.Id AND tag.Tag = 5;
This should work fine. However, recently I was digging through Postgres manuals and found that you can store arrays of stuff in a column. Using this, I could completely get rid of ThreadTags and have a table like this:
create table Threads (
Id uuid not null,
Posted timestamp not null,
Subject varchar(255) not null,
Replies int4 not null,
PosterId uuid not null,
Tags int2[],
primary key (Id)
);
and then find threads using the ANY function:
select * from Threads where 5 = ANY (Tags);
To me this seems cleaner, but I'm wondering about performance. If I had millions of threads, is a JOIN going to be faster? I guess what I'm asking about is the underlying implementation of ANY. Is it doing a sequential search? Can I index Tags and will ANY() then use that index? Any other opinions on what option is better?
One thing about the array approach is I'm using NHibernate which doesn't really seem to want to support Postgres arrays without a whole bunch of custom driver code and IUserTypes and junk, so I'd like to make sure this architecture is best before I commit to it. Thanks!!
Mike