Re: FN-FORUM: Database structure question

date posted 30th April 2007 16:20

Ben Johnson (Neogic) F wrote:
> Does anyone know how performance compares using an array of foreign keys
> (the "set" datatype Ian mentions) vs using a traditional link table for
> representing many-to-many relationships?
>

Performance is presumably quite good as sets seem to be implemented as a
large bitfield rather than having to perform lookups for different
values, but there's two huge drawbacks - you're only allowed 64 members
in a set, and you have to alter the table definition to add a new
member. Neither of those limitations apply if you use link tables.

Dave