Proliferation of custom fields

[Bradley Baetz]

On Wed, Nov 11, 2009 at 3:34 PM, David Miller <justdave at bugzilla.org> wrote:
> So, it shouldn't be any surprise that custom fields actually get used
> now that they're available.  We're starting to add quite a few of them
> for specific purposes at Mozilla.
>
> When you add a custom field that isn't a multi-select or a map, (just a
> string or a date for example) it gets added as a column to the bugs
> table.  I've got a little voice in the back of my head telling me that's
> going to hurt eventually if we keep adding fields.  Is my little voice
> correct, or have I nothing to worry about?  Is there a better way to
> architect it in the long run that won't cause as much damage?  Maybe a
> second table for bugs_custom that is only joined once by bug_id and is
> otherwise just custom fields for bugs?  That only buys you so much time,
> too, but potentially less stress on things that look at bugs and don't
> touch custom fields...

I disliked the idea of adding fields to the bugs table originally, but
didn't have a better idea.

Pros:

 * don't have to join to lots of tables. Big plus given mysql's
optmizer, plus locality of data, etc
 * Some of the buglist stuff becomes a bit more painful once you need
to handle aggregates
 * We can easily have FK constraints enforced on the data
 * Any 'bugs_custom' is going to have to be looked up almost any time
you wanted to read the bugs table anyway
 * More precise indexes (ie on each column rather than a mega
(fielddef,value) index - again, better cache size, etc

Cons:

 * Schema changes through the web interface feel icky (especially the
error cases, and script timeouts, and etc, since this mostly can't
happen in a transaction)
 * Extra data that isn't needed all the time is present
 * full table scans take longer because there is more data (of course
a full table scan is almost always a bug)
 * It let us not have to think about what happens to data as a bug is
moved between components/products/etc. (e.g buglist.cgi on a cf may
show bugs where that CF doesn't apply) As the dependant field stuff
gets more complicated this may have to be revisited anyway.

Bradley