Custom fields schema

[Max Kanat-Alexander]

On Thu, 2005-01-27 at 06:03 -0800, Myk Melez wrote:
> In my experience it's just as dangerous to overgenericize because
> someone might need some capability you don't know about yet.

	Yeah, that's true.

> But FAC doesn't limit the use of custom fields by plugins, and it
> allows installations and plugins to create just as many and as varied
> custom fields as FAD.  How is FAD more extensible?

	Because it's easier for a developer who knows nothing about Bugzilla
internals (a plugin developer) to add and maintain rows than it is to
add and maintain columns.

> > 	As a single example, what if I created a single plugin that was
> > "Project Management for Bugzilla?"
> >   
> Ok, let's say you did.  How does that inform this debate?

	Because it spans across the three groups of fields that you mentioned.
And because it can require functionality that I can't possibly predict
at this point, without actually doing the design.

> [snip]
> > or even
> > having to modify the SELECT statement in a fashion that can't use
> > placeholders.
> >   
> How does FAC make it harder to use placeholders?

	FAC looks like this:

	SELECT $column1, $column2, $column3, etc.
	FROM $table1 $possibleJoin1 $table2 $possibleJoin2
	WHERE <criteria>

	FAD looks like this:

	SELECT field_name, field_id, field_data, bug_id
	FROM field_table <some_standard_joins>
	WHERE field_name = $fieldname

	Only $fieldname can be a placeholder. You can't use placeholders for
column or table names.

	Generally, this is also what I meant by generic SQL.

	I think it's easier to deal with the FAD query that I wrote above, for
fields that I don't know about in advance, than it is to deal with the
FAC query. 

	The FAC query is great for fields that I know about in advance, because
I know what the field names will be, when I'm working with it in perl,
and for various other reasons.

	I'll admit, FAD does create a bit of a flat namespace, and that can be
a problem. Without proper indexes, it can create a large table that's
hard to parse. However, thankfully, the table structure is definite and
the indexes are easy to write. With FAC, there are also a larger number
of indexes, and it would be somewhat difficult to change them if we had
to, on an upgrade or something like that (that is, assuming that certain
columns had their own tables).
 
> It's possible to implement custom fields via either approach, and it's
> likely that either approach will work on a given installation, but
> we're trying to figure out the best approach for all Bugzilla
> installations and developers, and while the success of an approach on
> one site can inform the debate, it can't decide it.

	OK, I definitely agree with that.

> While weighing Sean's success with FAD at his installation, we should
> also weigh the success of FAC on hundreds of Bugzilla installations
> for a number of years, both for standard fields and for custom ones,
> not to mention the general success of FAC in database design.

	True. Of course, in most of those implementations, each developer knows
exactly what specific fields they are adding, and exactly how they're
going to be used. So they're not so much "generic" custom fields as they
are "specific" custom fields.

> longdescs isn't FAD at all, it stores comment entities which have
> attributes represented by columns and whose rows each represent a
> unique instance of a comment.  It's a classic example of an object
> modeled by a table, just like the bugs, attachments, and users tables.

	OK, you're right about that. :-)

	FWIW, before a few weeks ago, I was also a very strong proponent of
FAC, because it's better database design, for the backend.

	I just think that FAC will allow us to accomplish our goals for
Bugzilla more easily.

	-Max
-- 
Max Kanat-Alexander
Technical Support Manager, USA
2350 Mission College Blvd., Suite 400
Santa Clara, CA 95054
Phone: (408) 496-4500
Fax: (408) 496-6902
http://www.kerio.com/support.html