Custom entities and fields in a multi-tenant database system
Systems and methods for hosting variable schema data such as dynamic tables and columns in a fixed physical database schema. Standard objects, such as tables are provided for use by multiple tenants or organizations in a multi-tenant database system. Each organization may add or define custom fields for inclusion in a standard object. Custom fields for multiple tenants are stored in a single field within the object data structure, and this single field may contain different data types for each tenant. Indexing columns are also provided, wherein a tenant may designate a field for indexing. Data values for designated fields are copied to an index column, and each index column may include multiple data types. Each organization may also define custom objects including custom fields and indexing columns. Custom objects for multiple tenants are stored in a single custom object data structure. The primary key values for the single custom object table are globally unique, but also include an object-specific identifier which may be re-used among different entities.
1. A computer-implemented method of storing multiple tables for one or more tenants in a single data structure, comprising:
defining a data structure having a primary key column, an organization id column and a plurality of data columns;
defining a first table for a first tenant, said first table having a first data field, said first data field having a first data type, and said first tenant having a first tenant id;
assigning a first table id to the first table;
defining a second table for the first tenant, said second table having a second data field, said second data field having a second data type different from the first data type; and
assigning a second table id to the second table;
wherein when records are created for the first table, for each created record:
a) storing the value of the first data field to a single data column in the data structure,
b) storing the first tenant id in the organization id column, and
c) storing the first table id to the primary key column;
wherein when records are created for the second table, for each created record:
a) storing the value of the second data field to said single data column,
b) storing the first tenant id in the organization id column, and
c) storing the second table id to the primary key column;
wherein the first and second tables of the first tenant are stored in the data structure, and said single data column includes data values having said first and second data types;
defining a third table for a second tenant, said third table having a third data field, said third data field having a third data type, and said second tenant having a second tenant id; and
assigning a third table id to the third table;
wherein when records are created for the third table, for each created record:
a) storing the value of the third data field to said single data column in the data structure;
b) storing the second tenant id in the organization id column; and
c) storing the third table id to the primary key column;
wherein the third table is stored in the data structure;
wherein said single data column includes data values having said first and second data types and said third data type;
wherein the first and second table ids are different; and
wherein the third table id is the same as one of the first and second table ids.