Custom fields schema

[Myk Melez]

Sean McAfee wrote:

>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.
>  
>
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.

>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?
>  
>
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.

>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?
>  
>
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:

CREATE TABLE status_whiteboard(bug_id MEDIUMINT PRIMARY KEY, 
status_whiteboard MEDIUMTEXT NOT NULL);
INSERT INTO status_whiteboard(bug_id, status_whiteboard) SELECT bug_id, 
status_whiteboard FROM bugs where status_whiteboard IS NOT NULL;
UPDATE fielddefs SET [appropriate columns] WHERE name = 'status_whiteboard';
ALTER TABLE bugs DROP column status_whiteboard;

>My design handles all such scenarios equally well, and requires neither
>prescience nor hundreds of judgment calls on the part of the administrator.
>  
>
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.

>What current standard fields live in their own tables?
>
duplicates.dupe_of, for one.

>>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.
>>    
>>
>
>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.
>  
>
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.

-myk

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