Database schema for efficient data assessment

• US 10,387,388 B2
• Filed: 02/21/2017
• Issued: 08/20/2019
• Est. Priority Date: 01/04/2017
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Doan, Dai Duong, Vaishnav, Parth, Dvinov, Danil, Ker, Chenghung
• Primary Examiner(s): Willoughby, Alicia M
• Application Number:
  US15/438,434
• Publication Number:
  US 20180189330A1
• Time in Patent Office: 910 Days
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof

Abstract

A computing system may create a database schema representing tables for data assessment, organization identification (ID), rule ID, data source ID, and data assessment ID. The data assessment table may include a first field representing an organization ID, a second field representing a rule ID, a third field representing a data source ID, wherein the fields correspond to a records in the organization ID table, rule ID table and data source ID table, respectively. The data assessment table may also include a fourth field representing a match status indicating whether a record in data assessment ID table matches a record of a data source associated with the third field, a fifth field representing a data assessment record ID and corresponding to a record in data assessment record ID table, and a sixth field configured as a column family and representing a field of the business object.

Claims

1. A method comprising:

creating, by a computing system, a database schema associated with a non-relational distributed database, the database schema representing a data assessment table, an organization identifications (ID) table, a rule ID table, a data source ID table, and a data assessment ID table, the data assessment table having multiple fields, the multiple fields comprising;

a first field representing an organization ID and corresponding to a record in the organization ID table;

at least one second field representing a rule ID and corresponding to a record in the rule ID table, each record in the rule ID table being associated with a business object and indicating a rule to be used by a data assessment application;

at least one third field representing a data source ID and corresponding to a record in the data source ID table, each record in the data source ID table identifying a data source;

a fourth field representing a match status to indicate whether a record in the data assessment ID table matches a record of a data source associated with the third field;

a fifth field representing a data assessment record ID and corresponding to a record in the data assessment record ID table, each record in the data assessment record ID table being a record to be assessed; and

at least one sixth field configured as a column family and representing a field of the business object to be determined at run time by the data assessment application;

assessing, by the data assessment application, the data using the database schema and one or more data sources.

2. The method of claim 1, wherein the data assessment table is configurable to include the multiple fields in a specific order.

3. The method of claim 1, wherein each record in the organization ID table is configured to represent a unique organization of multiple organizations in a multi-tenant environment, and wherein the first field of the data assessment table is used to prevent data associated with one organization to be accessible by another organization.

4. The method of claim 3, wherein the non-relational distributed database is Hbase database.

5. The method of claim 4, wherein the at least one second field representing the rule ID comprises a first rule ID and a second rule ID, and wherein the data assessment application is configured to use the first rule ID to perform data assessment on a record in the data assessment record ID table and to use the second rule ID to perform data assessment on the same record in the data assessment record ID table.

6. The method of claim 5, wherein the at least one third field representing the data source ID comprises a first data source ID and a second data source ID, and wherein the data assessment application is configured to use the first data source ID to perform data assessment on a first field of a record in the data assessment record ID table and to use the second data source ID to perform data assessment on a second field of the same record in the data assessment record ID table.

7. The method of claim 6, wherein the data assessment record ID table is configured to accommodate records of different data shapes for different organizations.

8. The method of claim 7, wherein the data assessment record ID table is configured to accommodate changes to a data shape to records associated with an organization.

9. The method of claim 6, where in the at least one sixth field configured as a column family comprises a first column family associated with a first field of the business object and a second column family associated with a second field of the business object.

10. The method of claim 9, wherein the first column family is configured to include at least three columns comprising an aggravated value column, a field difference column, and a raw value column, the aggravated value column configured to store a category value corresponding to one of multiple categories associated with the first field of the business object, the field difference column configured to indicate whether the first field of the business object is blank, non blank and whether the first field of the business object matches with a corresponding field of a data source, the raw value column configured to store a value of the first field of the business object.

11. The method of claim 1, wherein the data assessment application and the non-relational distributed database are hosted in an environment configured to enable internal authentication scheme between the data assessment application and the non-relational distributed database.

12. An apparatus comprising:

one or more processors; and

a non-transitory computer readable medium storing a plurality of instructions, which when executed, cause the one or more processors to;

create, by a computing system, a database schema associated with a non-relational distributed database, the database schema representing a data assessment table, an organization identifications (ID) table, a rule ID table, a data source ID table, and a data assessment ID table, the data assessment table having multiple fields, the multiple fields comprising;

a first field representing an organization ID and corresponding to a record in the organization ID table;

at least one second field representing a rule ID and corresponding to a record in the rule ID table, each record in the rule ID table being associated with a business object and indicating a rule to be used by a data assessment application;

at least one third field representing a data source ID and corresponding to a record in the data source ID table, each record in the data source ID table identifying a data source;

a fourth field representing a match status to indicate whether a record in the data assessment ID table matches a record of a data source associated with the third field;

a fifth field representing a data assessment record ID and corresponding to a record in the data assessment record ID table, each record in the data assessment record ID table being a record to be assessed; and

at least one sixth field configured as a column family and representing a field of the business object to be determined at run time by the data assessment application;

assessing, by the data assessment application, the data using the database schema and one or more data sources.

13. The apparatus of claim 12, wherein the data assessment table is configurable to include the multiple fields in a specific order.

14. The apparatus of claim 12, wherein each record in the organization ID table is configured to represent a unique organization of multiple organizations in a multi-tenant environment, and wherein the first field of the data assessment table is used to prevent data associated with one organization to be accessible by another organization.

15. The apparatus of claim 14, wherein the non-relational distributed database is Hbase database.

16. The apparatus of claim 15, wherein the at least one second field representing the rule ID comprises a first rule ID and a second rule ID, and wherein the data assessment application is configured to use the first rule ID to perform data assessment on a record in the data assessment record ID table and to use the second rule ID to perform data assessment on the same record in the data assessment record ID table.

17. The apparatus of claim 16, wherein the at least one third field representing the data source ID comprises a first data source ID and a second data source ID, and wherein the data assessment application is configured to use the first data source ID to perform data assessment on a first field of a record in the data assessment record ID table and to use the second data source ID to perform data assessment on a second field of the same record in the data assessment record ID table.

18. The apparatus of claim 17, wherein the data assessment record ID table is configured to accommodate records of different data shapes for different organizations.

19. The apparatus of claim 18, wherein the data assessment record ID table is configured to accommodate changes to a data shape to records associated with an organization.

20. The apparatus of claim 17, where in the at least one sixth field configured as a column family comprises a first column family associated with a first field of the business object and a second column family associated with a second field of the business object.

21. The apparatus of claim 20, wherein the first column family is configured to include at least three columns comprising an aggravated value column, a field difference column, and a raw value column, the aggravated value column configured to store a category value corresponding to one of multiple categories associated with the first field of the business object, the field difference column configured to indicate whether the first field of the business object is blank, non blank and whether the first field of the business object matches with a corresponding field of a data source, the raw value column configured to store a value of the first field of the business object.

22. The apparatus of claim 12, wherein the data assessment application and the non-relational distributed database are hosted in an environment configured to enable internal authentication scheme between the data assessment application and the non-relational distributed database.

23. A computer program product comprising computer-readable program code to be executed by one or more processors when retrieved from a non-transitory computer-readable medium, the program code including instructions to:

create, by a computing system, a database schema associated with a non-relational distributed database, the database schema representing a data assessment table, an organization identifications (ID) table, a rule ID table, a data source ID table, and a data assessment ID table, the data assessment table having multiple fields, the multiple fields comprising;

a first field representing an organization ID and corresponding to a record in the organization ID table;

at least one second field representing a rule ID and corresponding to a record in the rule ID table, each record in the rule ID table being associated with a business object and indicating a rule to be used by a data assessment application;

at least one third field representing a data source ID and corresponding to a record in the data source ID table, each record in the data source ID table identifying a data source;

a fourth field representing a match status to indicate whether a record in the data assessment ID table matches a record of a data source associated with the third field;

a fifth field representing a data assessment record ID and corresponding to a record in the data assessment record ID table, each record in the data assessment record ID table being a record to be assessed; and

at least one sixth field configured as a column family and representing a field of the business object to be determined at run time by the data assessment application;

assessing, by the data assessment application, the data using the database schema and one or more data sources.

24. The computer program product of claim 23, wherein the data assessment table is configurable to include the multiple fields in a specific order.

25. The computer program product of claim 23, wherein each record in the organization ID table is configured to represent a unique organization of multiple organizations in a multi-tenant environment, and wherein the first field of the data assessment table is used to prevent data associated with one organization to be accessible by another organization.

26. The computer program product of claim 25, wherein the non-relational distributed database is Hbase database.

27. The computer program product of claim 26, wherein the at least one second field representing the rule ID comprises a first rule ID and a second rule ID, and wherein the data assessment application is configured to use the first rule ID to perform data assessment on a record in the data assessment record ID table and to use the second rule ID to perform data assessment on the same record in the data assessment record ID table.

28. The computer program product of claim 27, wherein the at least one third field representing the data source ID comprises a first data source ID and a second data source ID, and wherein the data assessment application is configured to use the first data source ID to perform data assessment on a first field of a record in the data assessment record ID table and to use the second data source ID to perform data assessment on a second field of the same record in the data assessment record ID table.

29. The computer program product of claim 28, wherein the data assessment record ID table is configured to accommodate records of different data shapes for different organizations.

30. The computer program product of claim 29, wherein the data assessment record ID table is configured to accommodate changes to a data shape to records associated with an organization.

31. The computer program product of claim 28, where in the at least one sixth field configured as a column family comprises a first column family associated with a first field of the business object and a second column family associated with a second field of the business object.

32. The computer program product of claim 31, wherein the first column family is configured to include at least three columns comprising an aggravated value column, a field difference column, and a raw value column, the aggravated value column configured to store a category value corresponding to one of multiple categories associated with the first field of the business object, the field difference column configured to indicate whether the first field of the business object is blank, non blank and whether the first field of the business object matches with a corresponding field of a data source, the raw value column configured to store a value of the first field of the business object.

33. The computer program product of claim 23, wherein the data assessment application and the non-relational distributed database are hosted in an environment configured to enable internal authentication scheme between the data assessment application and the non-relational distributed database.