<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Sean McAfee wrote:
<blockquote cite="mid20041215053536.86A61BC60@mail.schwag.org"
type="cite">
<pre wrap="">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.
</pre>
</blockquote>
<br>
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:<br>
<ul>
<li>links between columns (i.e. attributes of bugs) and fields (the
values of columns for particular bugs): the "field_id" column;</li>
<li>links between fields and their bugs: the "bug_id" column;<br>
</li>
<li>field values: the "value" column.</li>
</ul>
But MySQL already provides:<br>
<ul>
<li>links between columns and fields: columns;</li>
<li>links between fields and their bugs: fields;</li>
<li>field values: field values.</li>
</ul>
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.<br>
<br>
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.<br>
<br>
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.<br>
<br>
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.<br>
<br>
-myk<br>
<br>
</body>
</html>