Custom Group, Field, Value

As mentioned by Lobo in his blog http://civicrm.org/node/150,

The drawbacks for current custom data model are:
    * Custom values are stored in a thin table, one per value. This table will grow significantly for large number of custom values
    * Primary Export is not possible since it will exceed the mysql JOIN limit of 61 tables
    * You cannot make a large number of these custom fields searchable, else search will exceed the mysql JOIN limit
    * There is potentially a slow down with operations involving large number of custom fields.

We also discussed about CCK model and concluded, that we might not follow the exact model of CCK at this moment, but we do would like to change the model to address some or if possible all the above issues.

Custom Group

Current Table Schema - civicrm_custom_group

Field	Type	Null	Key	Default	Extra
id	int(10) unsigned	NO	PRI		auto_increment
domain_id	int(10) unsigned	NO
name	varchar(64)	YES		NULL
title	varchar(64)	YES		NULL
extends	enum ( 'Contact', 'Individual', 'Household', 'Organization', 'Location', 'Address', 'Contribution', 'Activity', 'Relationship', 'Phonecall', 'Meeting', 'Group', 'Membership', 'Participant', 'Event')	YES		Contact
extends_entity_column_name	varchar(64)	YES	MUL	NULL
extends_entity_column_value	varchar(64)	YES	MUL	NULL
style	enum('Tab', 'Inline')	YES		NULL
collapse_display	int(10) unsigned	YES		0
help_pre	text	YES		NULL
help_post	text	YES		NULL
weight	int(11)	No		1
is_active	tinyint(4)	YES		NULL

New Schema - civicrm_custom_group

We can add "table_name" (type: varchar) as a new column, whose value will be decided based on extends field.
Add a new column "is_multiple" (type: boolean), to indicate if the group has multiple values. Note that the model is the entire "group" has multiple values, so all the fields are repeating in that group.

Custom Field

Current Table Schema - civicrm_custom_field

Field	Type	Null	Key	Default	Extra
id	int(10) unsigned	NO	PRI		auto_increment
custom_group_id	int(10) unsigned	NO	FK to civicrm_custom_group
name	varchar(64)	YES		NULL
label	varchar(64)	YES		NULL
data_type	enum('String', 'Int', 'Float', 'Money', 'Memo', 'Date', 'Boolean', 'StateProvince', 'Country', 'File', 'Link')	YES		NULL
html_type	enum('Text', 'TextArea', 'Select', 'Multi-Select', 'Radio', 'CheckBox', 'Select Date', 'Select State/Province', 'Select Country', 'File', 'Link')	YES	MUL	NULL
default_value	varchar(255)	YES	MUL	NULL
is_required	tinyint(4)	YES		NULL
is_searchable	tinyint(4)	YES		0
is_search_range	tinyint(4)	No		1
weight	int(11)	YES		NULL
help_pre	text	YES		NULL
help_post	text	YES		NULL
mask	varchar(64)	YES		NULL
attributes	varchar(64)	YES		NULL
javascript	varchar(64)	YES		NULL
is_active	tinyint(4)	YES		NULL
options_per_line	int(10) unsigned	YES		NULL
start_date_years	int(10) unsigned	YES		NULL
end_date_years	int(10) unsigned	YES		NULL
date_parts	varchar(255)	YES		NULL
note_columns	int(10) unsigned	YES		NULL
note_rows	int(10) unsigned	YES		NULL

New Schema - civicrm_custom_field

Add another column "option_group_id" in order to make use of existing "civicrm_option_group" and "civicrm_option_value" and thereby eliminating the need of table "civicrm_custom_option". Also since we will be using a new table "civicrm_custom_value_groupName" for storing field values for a particular group (groupName), table "civicrm_custom_value" will also be not required.
Add two columns "table_name" and "column_name" (type: varchar).

Obsoleted tables

civicrm_custom_value
civicrm_custom_option

New Table(s)

For each custom group, a new "value" table is automatically created by the system. The name of this table will be: civicrm_custom_value_CLEANED_UP_GROUP_NAME. Each table will have an FK to the object that it is extending. In the "single value" case, this FK has a unique constraint. In addition to the standard fields ( id, extend_id) the other columns and type are determined by the custom fields that belong to the custom group. The column name will be based on the field ID.

Field	Type	Null	Key	Default	Extra
id	int(10) unsigned	NO	PRI		auto_increment
entity_id	int(10) unsigned	NO	FK to extended table
field_name_1	dynamic (based on data_type, html_type ..)	YES		NULL
field_name_N	dynamic (based on data_type, html_type ..)	YES		NULL

Points to be discussed

The presence of multiple entries for same extend_id would indicate if the group/field has multiple values. Shouldn't we always assume that there could be multiple values for a group ? so do we really need "is_multiple" column in "civicrm_custom_group" table ?
For multiple values by field, we can have rows with value for only that field which requires to have multiple value. For e.g considering the below given case, there are 2 values for field_name_1, 3 values for field_name_2 and 1 value for field_name_3.

id

entity_id

field_name_1

field_name_2

field_name_3

1

25

23

03-03-1984

1

2

25

24

04-03-1984

NULL

3

25

NULL

05-03-1984

NULL