Custom entities and fields in a multi-tenant database system

• US 9,092,501 B2
• Filed: 08/17/2012
• Issued: 07/28/2015
• Est. Priority Date: 04/02/2004
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Weissman, Craig, Wong, Simon
• Primary Examiner(s): Alam, Hosain
• Assistant Examiner(s): HARPER, ELIYAH STONE
• Application Number:
  US13/589,020
• Publication Number:
  US 20120310989A1
• Time in Patent Office: 1,075 Days
• Field of Search: 707/999.101, 707/790, 707/999.102
• US Class Current: 1/1
• 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 custom data column within the object data structure, and this single custom data column may contain different data types for each tenant.

Claims

1. A method, comprising:

defining a multi-tenant data structure including a data table having a plurality of data columns and a plurality of rows, wherein at least two of the rows of the data table are each specific to a different tenant and store data for the specific tenant;

for a single one of the data columns;

receiving from a first tenant a first definition for the data column, the first definition designating the data column for storing data that is of a first data type, and

receiving from a second tenant a second definition for the data column, the second definition designating the data column for storing data that is of a second data type, wherein the second data type is different than the first data type;

storing, in a metadata table of the multi-tenant data structure that is separate from the data table, the first definition for the data column and the second definition for the data column by;

storing in a first row of the metadata table an identifier of the first tenant, an identifier of the data column, and an identifier of the first data type, and

storing in a second row of the metadata table an identifier of the second tenant, the identifier of the data column, and an identifier of the second data type;

storing by the first tenant in a first data field of the data column a first data value of the first data type, and storing by the second tenant in a second data field of the data column a second data value of the second data type, such that the single data column includes data values having different data types for different tenants.

2. The method of claim 1, further comprising:

determining at least one of the first data field or the second data field has been flagged for indexing; and

storing, in the metadata table, an indication that the at least one of the first data field or the second data field has been flagged for indexing.

3. The method of claim 1, further comprising storing an indication of the data type corresponding to each of the first and second definitions in extensible markup language (XML) format.

10. The method of claim 1, wherein a second one of the columns of the table is specific to an identifier of the tenants, such that a third data field of the second column in the first row specific to the first tenant stores an identifier of the first tenant and a fourth data field of the second column in the second row specific to the second tenant stores an identifier of the second tenant.

11. The method of claim 1, wherein the first data type and the second data type each are different ones of a string, a number, a date, and a picklist.

12. The method of claim 1, further comprising:

providing access to the first data value stored in the first data field by reading the first data type stored in the first row of the metadata table and providing the first data value in a format indicated by the first data type; and

providing access to the second data value stored in the second data field by reading the second data type stored in the second row of the metadata table and providing the second data value in a format indicated by the second data type.

4. A non-transitory computer-readable medium storing code, the code comprising instructions for:

defining a multi-tenant data structure including a data table having a plurality of data columns and a plurality of rows, wherein at least two of the rows of the data table are each specific to a different tenant and store data for the specific tenant;

for a single one of the data columns;

receiving from a first tenant a first definition for the data column, the first definition designating the data column for storing data that is of a first data type, and

receiving from a second tenant a second definition for the data column, the second definition designating the data column for storing data that is of a second data type, wherein the second data type is different than the first data type;

storing, in a metadata table of the multi-tenant data structure that is separate from the data table, the first definition for the data column and the second definition for the data column by;

storing in a first row of the metadata table an identifier of the first tenant, an identifier of the data column, and an identifier of the first data type, and

storing in a second row of the metadata table an identifier of the second tenant, the identifier of the data column, and an identifier of the second data type;

storing by the first tenant in a first data field of the data column a first data value of the first data type, and storing by the second tenant in a second data field of the data column a second data value of the second data type, such that the single data column includes data values having different data types for different tenants.

5. The non-transitory computer-readable medium of claim 4, the code further comprises instructions to:

determine at least one of the first data field or the second data field has been flagged for indexing; and

store, in the metadata table, an indication that the at least one of the first data field or the second data field has been flagged for indexing.

6. The non-transitory computer-readable medium of claim 4, the code further comprises instructions to store an indication of the data type corresponding to each of the first and second definitions in extensible markup language (XML) format.

7. A multi-tenant database system, comprising:

a database for storing multi-tenant data objects; and

a database management process configured for;

defining a multi-tenant data structure including a data table having a plurality of data columns and a plurality of rows, wherein at least two of the rows of the data table are each specific to a different tenant and store data for the specific tenant;

for a single one of the data columns;

receiving from a first tenant a first definition for the data column, the first definition designating the data column for storing data that is of a first data type, and

receiving from a second tenant a second definition for the data column, the second definition designating the data column for storing data that is of a second data type, wherein the second data type is different than the first data type;

storing, in a metadata table of the multi-tenant data structure that is separate from the data table, the first definition for the data column and the second definition for the data column by;

storing in a first row of the metadata table an identifier of the first tenant, an identifier of the data column, and an identifier of the first data type, and

storing in a second row of the metadata table an identifier of the second tenant, the identifier of the data column, and an identifier of the second data type;

storing by the first tenant in a first data field of the data column a first data value of the first data type, and storing by the second tenant in a second data field of the data column a second data value of the second data type, such that the single data column includes data values having different data types for different tenants.

8. The multi-tenant database system of claim 7, wherein the database management process is further configured to:

determine at least one of the first data field or the second data field has been flagged for indexing; and

store, in the metadata table, an indication that the at least one of the first data field or the second data field has been flagged for indexing.

9. The multi-tenant database system of claim 7, wherein the database management process is further configured to store an indication of the data type corresponding to each of the first and second definitions in extensible markup language (XML) format.