Custom fields schema

[Myk Melez]

Sean McAfee wrote:

>To kick things off (I hope), here are some tables from my custom fields
>schema, shorn of some of the niftier additions that have accrued over time.
>  
>

The basic issue with this approach is that it replicates infrastructure 
that MySQL and other databases already provide for us and that we use 
for non-custom fields.  In particular, the integer, shortstring, 
longstring, and date tables provide:

    * links between columns (i.e. attributes of bugs) and fields (the
      values of columns for particular bugs): the "field_id" column;
    * links between fields and their bugs: the "bug_id" column;
    * field values: the "value" column.

But MySQL already provides:

    * links between columns and fields: columns;
    * links between fields and their bugs: fields;
    * field values: field values.

MySQL also provides us tools for querying and modifying the structure of 
tables in the database so that we can build tools to manage the columns, 
and its search and other algorithms are designed with the assumption 
that databases are built using its architecture for structuring and 
relating data.

So I'm loathe to replicate this architecture.  I think we're better off 
making custom fields be real columns in the database, just like standard 
fields, and creating independent value list and mapping tables for 
select and multi-select custom fields, just as we do for standard fields 
of those types.

Besides making custom fields work like standard fields, simplifying 
their integration with existing code and the migration of fields from 
standard to custom (and vice versa), this approach minimizes the 
side-effect risk associated with building independent field management 
on top of MySQL's existing infrastructure and the development cost of 
maintaining two ways of accessing and manipulating fields.

Of course, there are some things MySQL and other DBMSes don't provide, 
like column -> title mappings and product-specificity.  Those things 
should rightly be tackled with tables like the ones in this proposal, 
custom_fields and cf_membership, respectively, just as we currently 
tackle them for standard fields with tables like fielddefs and 
flag(in|ex)clusions.

-myk

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bugzilla.org/pipermail/developers/attachments/20041215/cce2c810/attachment.html>