CUSTOM ENTITIES AND FIELDS IN A MULTI-TENANT DATABASE SYSTEM

• US 20100205227A1
• Filed: 04/21/2010
• Published: 08/12/2010
• Est. Priority Date: 04/02/2004
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Weissman, Craig, Wong, Simon
• Granted Patent: US 8,112,445 B2
• Time in Patent Office: Days
• US Class Current: 707/803
• CPC Class Codes: G06F 16/2264 Multidimensional index structures
• CPC Class Codes: G06F 16/2291 User-Defined Types; Storage management thereof
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 16/289 Object oriented databases
• CPC Class Codes: G06F 21/6218 to a system of files or objects, e.g. local or distributed file system or database
• CPC Class Codes: G06Q 10/063112 Skill-based matching of a person or a group to a task

Abstract

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.

Claims

1. A computer-implemented method of storing multiple fields for multiple tenants in a single multi-tenant data structure, comprising:

defining a multi-tenant data structure having a plurality of data columns and one or more index columns;

defining a first data field for a first tenant, said first field having a first data type;

defining a second data field for a second tenant, said second field having a second data type, wherein the second data type is different than said first data type;

when records having data values in the first and second fields are created by the first and second tenants, storing the data values of first and second fields to a single column in the data structure, wherein the single column includes data values having different data types for different tenants; and

copying to a first one of the index columns the data values stored in the single data column for the first field in response to a request from the first tenant to index data in the first data field.

2. The method of claim 1, further comprising:

defining a separate data structure having one or more columns; and

in response to an indication from one of the first tenant and the second tenant that data in the first data field or second data field, respectively, be unique, copying the data values stored in the single data column corresponding to the first data field or second data field, respectively, to a column in the separate data structure.

3. The method of claim 1, wherein copying includes converting the copied data values to a modified format.

4. The method of claim 3, wherein converting includes applying a case folding algorithm to the data values.

5. A computer-implemented method of hosting multiple tables for one or more organizations in a single multi-tenant data structure, comprising:

defining a multi-tenant 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, and said first tenant having a first tenant id;

assigning a first table id to the first table;

defining a second table for a second tenant, said second table having a second data field, and said second tenant having a second tenant id;

assigning a second table id to the second table;

wherein when records are created for the first table by the first tenant, 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; and

wherein when records are created for the second table by the second tenant, for each created record;

a) storing the value of the second 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 second table id to the primary key column; and

wherein the first and second tables of the first and second tenants are stored in the data structure.

6. The method of claim 5, wherein the data structure includes one or more index columns, the method further comprising:

copying to a first one of the index columns the data values stored in the single data column for the first table in response to a request from the first tenant to index data in the first data field.

7. The method of claim 6, wherein copying includes identifying the data values to be copied based on the first tenant id, the first table id and the first data field.

8. The method of claim 5, wherein said first data field has a first data type, and wherein said second data field has a second data type different from the first data type, such that said single data column includes data values having said first and second data types.

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

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; and

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 wherein said single data column includes data values having said first and second data types.

10. The method of claim 9, further comprising:

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;

storing the value of the third data field to said single data column in the data structure;

storing the second tenant id in the organization id column; and

storing the third table id to the primary key column;

wherein the first, second and third tables are stored in the data structure, and wherein said single data column includes data values having said first and second data types and said third data type.

11. The method of claim 9, wherein the first and second table ids are different.

12. The method of claim 10, 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.

13. The method of claim 9, wherein the data structure includes one or more index columns, the method further comprising:

copying to a first one of the index columns the data values stored in the single data column for the first table in response to a request from the first tenant to index data in the first data field.

14. The method of claim 13, wherein copying includes converting the copied data values to a modified format.

15. The method of claim 14, wherein converting includes applying a case folding algorithm to the data values.

16. The method of claim 9, wherein said third data type is selected from the group consisting of said first data type, said second data type and a data type different from the first and second data types.

17. The method of claim 9, wherein when the first tenant creates a record for the first table, executing a process that determines whether the data value in the first data field for that record satisfies a threshold criteria, and if so, processing an action rule.

18. The method of claim 17, wherein the action rule indicates a recipient of a notification, the method further including automatically sending a notification message to the recipient.

19. The method of claim 9, further including defining an owner field for the first data table, wherein each data value stored in the owner field indicates an hierarchical user access level for the associated record.

20. A computer readable medium storing code for controlling a database system to store multiple fields for multiple tenants in a single multi-tenant data structure, the code comprising instructions to:

define a multi-tenant data structure having a plurality of data columns and one or more index columns;

define a first data field for a first tenant, said first field having a first data type;

define a second data field for a second tenant, said second field having a second data type, wherein the second data type is different than said first data type;

store the data values of first and second fields to a single column in the data structure when records having data values in the first and second fields are created by the first and second tenants, wherein the single column includes data values having different data types for different tenants; and

copy to a first one of the index columns the data values stored in the single data column for the first field in response to a request from the first tenant to index data in the first data field.

21. A computer readable medium storing code for controlling a database system to store multiple fields for multiple tenants in a single multi-tenant data structure, the code comprising instructions to:

define a multi-tenant data structure having a primary key column, an organization id column and a plurality of data columns;

define a first table for a first tenant, said first table having a first data field, and said first tenant having a first tenant id;

assign a first table id to the first table;

define a second table for a second tenant, said second table having a second data field, and said second tenant having a second tenant id;

assign a second table id to the second table;

wherein when records are created for the first table by the first tenant, for each created record;

a) store the value of the first data field to a single data column in the data structure;

b) store the first tenant id in the organization id column; and

c) store the first table id to the primary key column; and

wherein when records are created for the second table by the second tenant, for each created record;

a) store the value of the second data field to said single data column in the data structure;

b) store the second tenant id in the organization id column; and

c) store the second table id to the primary key column; and

wherein the first and second tables of the first and second tenants are stored in the data structure.

22. A multi-tenant database system, comprising:

a database for storing multi-tenant data objects; and

a database management process configured to;

define a multi-tenant data structure in the database, the data structure having a plurality of data columns and one or more index columns;

define a first data field for a first tenant, said first field having a first data type;

define a second data field for a second tenant, said second field having a second data type, wherein the second data type is different than said first data type;

store the data values of first and second fields to a single column in the data structure when records having data values in the first and second fields are created by the first and second tenants, wherein the single column includes data values having different data types for different tenants; and

copy to a first one of the index columns the data values stored in the single data column for the first field in response to a request from the first tenant to index data in the first data field.

23. A multi-tenant database system, comprising:

a database for storing multi-tenant data objects; and

a database management process configured to;

define a multi-tenant data structure in the database, wherein the data structure has a primary key column, an organization id column and a plurality of data columns;

define a first table for a first tenant, said first table having a first data field, and said first tenant having a first tenant id;

assign a first table id to the first table;

define a second table for a second tenant, said second table having a second data field, and said second tenant having a second tenant id;

assign a second table id to the second table;

wherein when records are created for the first table by the first tenant, for each created record;

a) store the value of the first data field to a single data column in the data structure;

b) store the first tenant id in the organization id column; and

c) store the first table id to the primary key column; and

wherein when records are created for the second table by the second tenant, for each created record;

a) store the value of the second data field to said single data column in the data structure;

b) store the second tenant id in the organization id column; and

c) store the second table id to the primary key column; and

wherein the first and second tables of the first and second tenants are stored in the data structure