Modifying table definitions within a database application

• US 20070005630A1
• Filed: 06/29/2005
• Published: 01/04/2007
• Est. Priority Date: 06/29/2005
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Bryant, Christopher, Naramore, Theresa, Covington, Clinton, Selca, Vitore, Groff, Shane, Beyene, Tiginesh
• Granted Patent: US 7,716,168 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

Methods, systems, and computer program products modify a table definition of a database table within a database application. A method involves providing a user interface for creating a new field in the database table, receiving data that creates the new field while displaying the database table, and modifying schema of the database table to include the new field and the data. The method also involves interpreting a data type of the data as the data is received. A data type interpreted for a column in the database table that contains the new field is the data type of the data received in the new field. Still further, the method involves handling objects dependent on the database table in a manner unbeknownst to a user of the database table. Thus, the schema is modified while the objects dependent on the database table remain visible or open to the user.

Claims

1. A computer-implemented method for modifying a table definition of a database table within a database application, the method comprising:

providing a user interface for creating a new field in the database table;

while rendering the database table, receiving data that creates the new field via the user interface; and

modifying schema of the database table to include the new field.

2. The method of claim 1, wherein receiving the data via the user interface comprises at least one of the following:

receiving a selection of a header of a column in the database table and a name of the column, the header operative when selected to insert the new field in the column and receive the name of the column;

receiving input in a cell of the column; and

receiving a paste command in the column and pasting tabular data in the database table thereby inserting multiple new fields in the database table.

3. The method of claim 2, further comprising interpreting a data type of the data as the data is received in the new field.

4. The method of claim 3, wherein interpreting the data type comprises:

examining the first input received in a cell of the column; and

determining a data type of the column based on the first input.

5. The method of claim 3, wherein interpreting the data type comprises:

examining the tabular data pasted in the database table; and

determining a data type that encompasses all the tabular data per column.

6. The method of claim 4, further comprising:

receiving data in another cell of the column containing the new field;

in response to the data in another cell not being of a same data type as interpreted for the column, rendering a dialog providing notice that the data in another cell is not of the same data type interpreted for the column and presenting resolution options; and

in response to rendering the dialog, receiving and implementing an option for resolution of conflicting data types.

7. The method of claim 5, wherein determining the data type that encompasses all the tabular data comprises:

determining whether the tabular data includes currency;

in response to the tabular data including currency, determining whether a higher precision or one currency plus numbers are required;

in response to higher precision or one currency plus numbers being required, setting the data type as number;

in response to higher precision or one currency plus numbers not being required, determining whether the tabular data includes multiple currencies or a mixed data type;

in response to the tabular data not including multiple currencies or a mixed data type, setting the data type as currency;

in response to the tabular data including multiple currencies or a mixed data type, determining whether any cell containing tabular data contains text that exceeds a threshold number of characters;

in response to the text exceeding the threshold number, setting the data type as memo; and

in response to the text not exceeding the threshold number, setting the data type as text.

8. The method of claim 7, further comprising:

determining whether the tabular data includes numbers;

in response to the tabular data including numbers, setting the data type as number;

determining whether the tabular data includes at least one of a date or time;

in response to the tabular data including at least one of a date or time, setting the data type as at least one of date or time;

determining whether the tabular data includes a hyperlink;

in response to the tabular data including a hyperlink, setting the data type as hyperlink;

determining whether the tabular data includes Boolean values; and

in response to the tabular data including Boolean values, setting the data type as Boolean.

9. The method of claim 1, further comprising:

handling one or more objects dependent on the database table unbeknownst to a user of the database table wherein modifying the schema of the database table comprises modifying the schema of the database table while the objects dependent on the database table remain visible to the user.

10. The method of claim 9, wherein handling the objects dependent on the database table comprises:

examining a database for one or more objects utilizing the database table;

determining an object type and a view of each object utilizing the database table;

tracking a primary key identifier for a row in which the new field is created; and

handling each object utilizing the database table based on the object type and the view;

wherein the object types include at least one of the following;

another table having a lookup query to the database table;

a query;

a form having a record source that depends on the database table; and

a report; and

wherein the views include at least one of the following;

a design view;

a datasheet view;

a form view;

a report view;

a print preview view; and

a chart or pivot view.

11. The method of claim 10, wherein handling each object based on the object type and the view, comprises at least one of the following:

saving any edit in progress and after modifying the schema returning to a focus state rendered prior to modifying the schema when the view comprises the datasheet view and the object type comprises a table, a query, or a form or when the view comprises the form view and the object type comprises the form;

returning after modifying the schema to a page rendered just before modifying the schema when the view comprises the print preview view and the object type comprises the table, the query, the report or the form;

returning after modifying the schema to the focus state rendered just before modifying the schema when the view comprises the chart view or the pivot view and the object type comprises the table, the query, or the form;

closing a saved browse state rendered prior to modifying the schema when the view comprises the datasheet view and the object type comprises the report or the form; and

returning after modifying the schema to the focus state rendered just before modifying the schema when the view comprises the report view and the object type comprises the report;

wherein tracking the primary key identifier for the row in which the new field is created locates the focus state rendered just before modifying the schema.

12. A computer program product comprising a computer-readable medium having control logic stored therein for causing a computer to modify a table definition of a database table within a database application, the control logic comprising computer-readable program code for causing the computer to:

provide a user interface for creating a new field in the database table;

while displaying the database table, receive data that creates the new field via the user interface; and

modify schema of the database table to include the new field and the data.

13. The computer program product of claim 12, wherein the computer-readable program code for causing the computer to receive the data via the user interface comprises computer-readable program code for causing the computer to at least one of the following:

receive a selection of a header of a column in the database table and a name of the column, the header operative when selected to insert the new field in the column and receive the name of the column;

receive input in a cell of the column; and

receive a paste command in the column and paste tabular data in the database table thereby inserting multiple new fields in the database table.

14. The computer program product of claim 13, further comprising computer-readable program code for causing the computer to interpret a data type of the data as the data is received in the new field wherein a data type interpreted for the column comprises the data type of the data.

15. The computer program product of claim 14, further comprising computer-readable program code for causing the computer to:

receive data in another cell of the column containing the new field; and

in response to the data in another cell not being of a same data type as interpreted for the column, display a dialog providing notice that the data in the another cell is not of the same data type interpreted for the column the dialog including resolution options.

16. The computer program product of claim 12, further comprising computer-readable program code for causing the computer to:

handle one or more objects dependent on the database table unbeknownst to a user of the database table;

wherein the computer-readable program code for causing the computer to modify the schema comprises computer-readable program code for causing the computer to modify the schema while the objects dependent on the database table remain visible to the user.

17. The computer program product of claim 12, wherein the computer-readable program code for causing the computer to handle the objects dependent on the database table comprises computer-readable program code for causing the computer to comprises:

examine a database for one or more objects utilizing the database table;

determine an object type and a view of each object utilizing the database table;

track a primary key identifier for a row in which the new field is created; and

handle each object utilizing the database table based on the object type and the view.

18. A computer-implemented system for modifying a table definition of a database table within a database application, the system comprising:

means for providing a user interface for creating a new field in the database table;

means for receiving data that creates the new field via the user interface while displaying the database table; and

means for modifying schema of the database table to include the new field and the data.

19. The system of claim 18, further comprising means for interpreting a data type of the data as the data is received in the new field wherein a data type interpreted for a column containing the new field in the database table comprises the data type of the data.

20. The system of claim 18, further comprising means for handling one or more objects dependent on the database table unbeknownst to a user of the database table;

wherein the means for modifying the schema comprises means for modifying the schema while the objects dependent on the database table remain visible to the user.