Redmine custom field MySQL table structure

Redmine can create custom fields and I often use them to meet different management needs, now let's take a look at how these custom fields exist in the MySQL table.

Table Issues

The standard field used to store issue.

Mysql> describe issues;+----------------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------------------+--------------+------+-----+---------+----------------+| ID | Int (11) | NO | PRI | NULL | auto_increment | | tracker_id | Int (11) | NO | MUL |                NULL | || project_id | Int (11) | NO | MUL |                NULL | || Subject | varchar (255) |     NO |         |                | || Description | Text |     YES | |                NULL | || Due_date | Date |     YES | |                NULL | || category_id | Int (11) | YES | MUL |                NULL | || status_id | Int (11) | NO | MUL |                NULL | || assigned_to_id | Int (11) | YES | MUL |                NULL | || priority_id | INT (11) | NO | MUL |                NULL | || fixed_version_id | Int (11) | YES | MUL |                NULL | || author_id | Int (11) | NO | MUL |                NULL | || lock_version | Int (11) |     NO | |                0 | || created_on | datetime | YES | MUL |                NULL | || updated_on | datetime |     YES | |                NULL | || start_date | Date |     YES | |                NULL | || Done_ratio | Int (11) |     NO | |                0 | || estimated_hours | float |     YES | |                NULL | || parent_id | Int (11) |     YES | |                NULL | || root_id | Int (11) | YES | MUL |                NULL | || LfT | Int (11) |     YES | |                NULL | || RGT | Int (11) |     YES | |                NULL ||| Is_private | tinyint (1) |     NO | |                0 | || closed_on | datetime |     YES | |                NULL | || Position | Int (11) | NO | MUL |                NULL | || remaining_hours | float |     YES | |                NULL | || release_id | Int (11) | YES | MUL |                NULL | || story_points | float |     YES | |                NULL | || Release_relationship | varchar (255) | NO | MUL |                Auto | |+----------------------+--------------+------+-----+---------+----------------+

Table Custom_fields

This table field is similar to the selection that you see for the Web page that created the custom field.

Mysql> describe custom_fields;+-----------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-----------------+--------------+------+-----+---------+----------------+| ID | Int (11) | NO | PRI | NULL | auto_increment | | Type | varchar (30) |     NO |         |                | || name | varchar (30) |     NO |         |                | || Field_format | varchar (30) |     NO |         |                | || Possible_values | Text |     YES | |                NULL | || RegExp | varchar (255) |     YES |         |                | || Min_length | Int (11) |     YES | |                NULL | || Max_length | Int (11) |     YES | |                NULL | || is_required | tinyint (1) |     NO | |                0 | || Is_for_all | tinyint (1) |     NO | |                0 | || Is_Filter | tinyint (1) |     NO | |                0 | || Position | Int (11) |     YES | |                1 | || Searchable | tinyint (1) |     YES | |                0 | || Default_value | Text |     YES | |                NULL | || Editable | tinyint (1) |     YES | |                1 | || Visible | tinyint (1) |     NO | |                1 | || multiple | tinyint (1) |     YES | |                0 | || Format_store | Text |     YES | |                NULL | || Description | Text |     YES | |                NULL | |+-----------------+--------------+------+-----+---------+----------------+

Table Custom_values

Mysql> describe custom_values;+-----------------+-------------+------+-----+---------+----------------+| Field           | Type        | Null | Key | Default | Extra          |+-----------------+-------------+------+-----+---------+----------------+| ID              | int     | NO   | PRI | NULL    | auto_increment | | customized_type | varchar (30) | NO   | MUL | | | |                customized_id   -int |     NO   |     | 0       |                | | custom_field_id | int     | NO   | MUL | 0       |                | | value           | text        | YES  |     | NULL    |                | +-----------------+-------------+------+-----+---------+----------------+

The table can be associated with the ID of the custom_field_id field and the Custom_fields table.

And customized_id can be associated with the ID of the issues table

So the three tables issues, Custom_fields and custom_values together to express such a relationship.

A issue standard field comes from the issues table, the extended field is from the Custom_fields table, and the custom_values is associated with the former Custom_fields table, which together represents the value of one of the issue's custom fields.

Also, when representing a custom field of Issue, the value of Custom_fields.type is ' Issuecustomfield ' and the Custom_values.customized_type value is ' Issue '.

custom field values for all issue

Therefore, the Custom_fields table and the Custom_values table can be associated first to obtain the following results:

Mysql> Select customized_id as Issue_id,custom_field_id,type,name,default_value,value from Custom_fields a inner Join Custom_values b on a.id = b.custom_field_id limit 10;+----------+-----------------+------------------+----------- ---+---------------+-----------------------------+| issue_id | custom_field_id | Type | name | Default_value | Value |+----------+-----------------+------------------+--------------+---------------+-------------        ----------------+|               2 | 1 | Issuecustomfield | Shutdown Reason | ...           |      ...                         ||               121 | 1 | Issuecustomfield | Shutdown Reason | ...           |      Mobile Reverse Attack Complete | |               122 | 1 | Issuecustomfield | Shutdown Reason | ...           |      Do not do it temporarily | |               123 | 1 | Issuecustomfield | Shutdown Reason | ...           |      Staff turnover | |               124 | 1 | Issuecustomfield | Shutdown Reason | ...           |      Staff turnover | |          125 |     1 | Issuecustomfield | Shutdown Reason | ...           |      Staff turnover, Forum revision | |               126 | 1 | Issuecustomfield | Shutdown Reason | ...           |      Re-planning | |               127 | 1 | Issuecustomfield | Shutdown Reason | ...           |      Not involved for the time being | |               128 | 1 | Issuecustomfield | Shutdown Reason | ...           |      Not involved for the time being | |               129 | 1 | Issuecustomfield | Shutdown Reason | ...           | |+----------+-----------------+------------------+--------------+---------------+-----------------------are not currently involved ------+

Usually this table will be very large, my system has 22 custom fields, and there are more than 500 issue, each issue will have up to 22 rows to represent the value of their custom fields. Therefore, the values of all issue custom fields have more than 10,000 rows in the cumulative number of rows.

It can be seen that redmine is designed to represent the value of the extended field with the number of rows of records, so it is not limited by the MySQL table fields.

Redmine custom field MySQL table structure