<!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:<br>
<blockquote cite="mid20050128195638.3E3A5BC78@mail.schwag.org"
type="cite">
<pre wrap="">Sure, but that's just because it's the quickest way to hack on additional
fields. No one who only wanted a few more fields would seriously consider
going to the lengths I have to design and implement a new field
architecture. This precedent is in no way an argument for FAC over FAD.
</pre>
</blockquote>
I would, because I expect it to save me time and energy in the long
run, even if it costs me more up-front. Bugzilla has long needed the
rigor and reusability of custom fields code, even for its standard
fields. Of course, I wouldn't be designing a new field architecture,
I'd be writing code that handles custom fields within the current one.<br>
<br>
<blockquote cite="mid20050128195638.3E3A5BC78@mail.schwag.org"
type="cite">
<pre wrap="">So, our most heavily-populated product has only 61% of the bugs and 32% of
the fields. How should an administrator categorize this field or that as
"sparse" or "dense"? What if he didn't know in advance the relative
populations of the products?
</pre>
</blockquote>
It's not a tragedy if he guesses wrong, since the difference in
performance between separate-table FAC and one-table FAC isn't orders
of magnitude, and switching between the models is trivial.<br>
<br>
<blockquote cite="mid20050128195638.3E3A5BC78@mail.schwag.org"
type="cite">
<pre wrap="">Furthermore, the classification of a field as sparse or dense can change
over time. Consider the case of an installation that starts off with three
products, all of which share all custom fields. Per your design, the
administrator makes them "dense" fields, stored as columns in BUGS. Over
time seven more products are added, none of which require any of the
original fields. If all of the products have comparable numbers of bugs and
fields, the original fields' density shrinks from 100% to 30%. What then?
</pre>
</blockquote>
If it matters at that point, then we move the field to a separate
table, which is a trivial operation. For the status whiteboard field,
for example, it would look something like this:<br>
<br>
CREATE TABLE status_whiteboard(bug_id MEDIUMINT PRIMARY KEY,
status_whiteboard MEDIUMTEXT NOT NULL);<br>
INSERT INTO status_whiteboard(bug_id, status_whiteboard) SELECT bug_id,
status_whiteboard FROM bugs where status_whiteboard IS NOT NULL;<br>
UPDATE fielddefs SET [appropriate columns] WHERE name =
'status_whiteboard';<br>
ALTER TABLE bugs DROP column status_whiteboard;<br>
<br>
<blockquote cite="mid20050128195638.3E3A5BC78@mail.schwag.org"
type="cite">
<pre wrap="">
My design handles all such scenarios equally well, and requires neither
prescience nor hundreds of judgment calls on the part of the administrator.
</pre>
</blockquote>
Mine requires no prescience and a configurable (by us) number of
judgement calls on the part of the administrator. We could decide to
remove all calls from her purvey by picking the specific model
ourselves. But I think we're better off involving the administrator,
who knows better than we do what her data looks like, and who is better
off able to customize its storage.<br>
<br>
<blockquote cite="mid20050128195638.3E3A5BC78@mail.schwag.org"
type="cite">
<pre wrap="">What current standard fields live in their own tables?</pre>
</blockquote>
duplicates.dupe_of, for one.<br>
<br>
<blockquote cite="mid20050128195638.3E3A5BC78@mail.schwag.org"
type="cite">
<blockquote type="cite">
<pre wrap="">Bugzilla, Bonsai, and Despot, to name only some Mozilla apps. But of
course now I'm talking about the ER modeling approach outlined above,
not some one-table-per-field approach which I have never advocated.
</pre>
</blockquote>
<pre wrap=""><!---->
You have, as a possibility:
]Of course, we don't have to put all of these columns into the bugs
]table. We can do that, but we can also put each one into its own table
](so that bugs->custom field is a 1->0|1 relationship, and the database
]uses no more storage for the custom field than necessary), all of them
]into one other table, or some combination of these three, depending on
]what makes the most sense.
Also, your test program employed this type of schema, so I hope you can
understand my mistake.
</pre>
</blockquote>
Sure. In fact, I do suggest it as an option, just as I suggest putting
all fields into the bugs table, putting them all into one other table,
or grouping them into sets, with one table per set, as possibilities.
I consider these options strengths of the FAC approach, and prefer an
approach in which can be made available when useful.<br>
<br>
-myk<br>
<br>
</body>
</html>