Custom fields schema

[Sean McAfee]

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.

CREATE TABLE custom_fields (
    field_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    field_name VARCHAR(100) NOT NULL,
    display_name TINYTEXT NOT NULL,
    field_type CHAR BINARY NOT NULL,
    selection_id INTEGER,
    UNIQUE INDEX (field_name)
);

This is obviously the table which enumerates all custom fields defined at a
particular Bugzilla installation.

field_id is your basic primary key.

field_name is a unique name for the key, used in such contexts as CGI
parameters:

http://bugzilla.mycompany.com/buglist.cgi?my_custom_field=foo

...and names used in code:

print "field value is ", $bug->{my_custom_field}, "\n";

On ongoing but minor concern of mine has been how to guarantee that a custom
field's name does not conflict with any CGI parameter, now or in the future.
Adopting a naming convention, such as a short prefix followed by "_", has
seemed to suffice.

display_name is the field's name as it appears on Web pages.
my_custom_field's display name might be "My Custom Field", for example.

There has been disagreement on this mailing list in the past, since this
approach is not internationalization-friendly.  Recently it occurred to me
that a compromise might be to use templates to look up field display names,
as others have proposed (or insisted upon), but to fall back on the contents
of this column if no template-based name is found.

field_type obviously describes the type of the field.  Valid values are:

'i' - An integer.

'd' - A date with no associated time.

't' - A date/time.

's' - A short string, 255 or fewer characters in length.  No vertical
      whitespace allowed.  Represented on CGI forms by a single-line text
      element.

'l' - A long string, 65535 or fewer characters in length.  No restriction on
      contents.  Represented on CGI forms by a textarea element.

'e' - A "selection" field, which takes its values from a set of valid
      strings, its "domain".  Such a field's value may be either exactly one
      or zero of the elements of its domain.

'm' - A "multiselection" field.  Like a selection field, but its value may
      consist of any number of elements of its domain.

More recently, I've had a need to address custom fields and built-in
Bugzilla fields using the same range of integers, so I added an eighth field
type: 'b', for Bugzilla.

selection_id is non-null only for selection, multiselection, and Bugzilla
fields.  In the first two cases, it is an index into the table
CF_SELECTIONS (see below).  In the last case, it is an index into the
FIELDDEFS table.


CREATE TABLE cf_membership (
    product_id INTEGER NOT NULL REFERENCES products (id),
    field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
    sortkey INTEGER NOT NULL,
    INDEX (product_id)
);

This table describes which fields belong to which products, in what should
be a straightforward manner.  (The REFERENCES clauses have no effect for
most MySQL tables, but are useful for documentation purposes.)  The sort
order described here may be overridden or ignored outright in templates; it
is a default only.


CREATE TABLE cf_selections (
    selection_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    selection_name VARCHAR(100) NOT NULL,
    unset_label TINYTEXT NOT NULL,
    UNIQUE INDEX (selection_name)
);

This table describes the possible domains of all selection fields in the
installation.  selection_id is the primary key.  selection_name is a name
which is presented on administrative interfaces, and may be subject to the
same concerns as custom fields' display names.  unset_label is a textual
label which is printed when a selection field's value is null.  I created
this feature simply because it existed in my company's previous
incident-tracking system (TeamTrack), but it has proven to be of remarkably
little use, and I wouldn't be averse to ditching it.

Note that distinct custom fields may share the same selection domain.


CREATE TABLE cf_selection_labels (
    label_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    selection_id INTEGER NOT NULL REFERENCES cf_selections (selection_id),
    label VARCHAR(100) NOT NULL,
    inactive BOOL NOT NULL,
    sortkey INTEGER NOT NULL,
    UNIQUE INDEX (selection_id, label)
);

This table describes the elements (or "labels") of all selection domains.
label_id is the primary key.  selection_id describes to which domain each
label belongs, sorted by default in the order described by sortkey.  label
is the text of the element.  inactive is a boolean flag; if true, the
corresponding label may not be entered as a new field value.  This feature
is necessary because otherwise there would be no way to remove a selection
element without annihilating all existing data that refers to that element.


CREATE TABLE cf_integer (
    bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
    field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
    value INTEGER,
    PRIMARY KEY (bug_id, field_id),
    INDEX (field_id)
);

This table stores integer custom field data, in what again should be a
straightforward manner.  The primary key is useful for looking up field
values for known bugs; the index on field_id is useful for querying.  Note
that an integer field may be null, indicating that it has no value.

Similar comments apply to the next three tables:

CREATE TABLE cf_shortstring (
    bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
    field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
    value TINYTEXT,
    PRIMARY KEY (bug_id, field_id),
    INDEX (field_id)
);

CREATE TABLE cf_longstring (
    bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
    field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
    value TEXT,
    PRIMARY KEY (bug_id, field_id),
    INDEX (field_id)
);

CREATE TABLE cf_date (
    bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
    field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
    value DATETIME,
    PRIMARY KEY (bug_id, field_id),
    INDEX (field_id)
);

Both date and date/time field values are stored in the CF_DATE table.  Time
components of date field values are eliminated by the interface code when it
goes into or comes out of the database.  Interface code will accept, as user
input for date fields, any input that Date::Parse can handle.


CREATE TABLE cf_selection (
    bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
    field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
    label_id INTEGER NOT NULL REFERENCES cf_selection_labels (label_id),
    INDEX (bug_id, field_id),
    INDEX (field_id)
);

This table is similar to the others, but since selection fields are
multivalued, no primary key is possible.  Both selection and multiselection
fields are stored in the same table; the interface code must ensure that
single-selection fields do not refer to more than one element of their
domains.

So...comments?


-- 
Sean McAfee -- etzwane at schwag.org