Method and apparatus for storing and retrieving data in multiple languages simultaneously using a fully-populated sub-table

• US 5,787,410 A
• Filed: 02/20/1996
• Issued: 07/28/1998
• Est. Priority Date: 02/20/1996
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: McMahon, Douglas James
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): Coby, Frantz
• Application Number:
  US08/603,658
• Time in Patent Office: 889 Days
• Field of Search: 395/611, 364/200, 364/419.08, 707/1, 707/100, 707/3, 707/101
• US Class Current: 707/760
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

The present invention provides a computer implemented method and apparatus for storing and retrieving data in a database management system (DBMS) in multiple languages, simultaneously, using a fully-populated Sub-Table. In one embodiment, the method and apparatus include generating a Base Table and a Translation Sub-Table. The Base Table includes base rows, each having a primary key and zero or more untranslatable values, exclusive of translatable data values. The Translation Sub-Table includes sets of sub-table rows, wherein each set of sub-table rows corresponds to a base row in the Base Table. In each set of sub-table rows, a sub-table row is provided for each operational language having at least one translatable data value corresponding to the primary key of the respective base row.

Claims

1. A computer implemented method for storing and retrieving data values in a database management system (DBMS), said method comprising the steps of:

generating a Base Table, said Base Table including at least one base row having a primary key, said base row exclusive of translatable data values;

generating a fully-populated Sub-Table, said Sub-Table includes a set of fully-populated sub-table rows corresponding to said base row, each said fully-populated row includes at least one translatable data field, said Sub-Table further includes a set of language values, wherein each set of sub-table rows in said Sub-Table corresponds to a separate language value of said set of language values

selecting a language value from said set of language values as a default language;

inserting a second base row in said Base Table; and

in response to inserting said second base row in said Base Table, generating a second set of sub-table rows in said Sub-Table, each row of said second set corresponding to a separate language value of said set of language values, wherein at least one of said rows in said second set is generated by copying a translatable data value of said default language.

2. The method of claim 1, wherein the step of generating said second set of sub-table rows, further includes the step of:

marking invalid, said row generated by copying a translatable data value of said default language.

3. The method of claim 1, wherein in the step of generating said second set of sub-table rows, at least one of said sub-table rows is generated by inserting a translatable data value of a language value different from said default language.

4. The method of claim 3, wherein the step of generating a set of sub-table rows, further includes the steps of:

marking valid, said row generated by inserting translatable data value of said language value different from said default language.

5. The method of claim 4, further including the steps of:

inserting a third base row in said Base Table, said third base row having a primary key;

generating, in response to inserting said third base row in said Base Table, a third set of sub-table rows in said Sub-Table corresponding to said third base row, said third set of sub-table rows including a row corresponding to each language value of said set of language values and at least one translatable data value.

6. The method of claim 5, further including the steps of:

inserting in said set of language values of said Sub-Table, a new language value;

generating, in response to inserting said new language value, a set of sub-table rows corresponding to said new language, wherein each row of said set includes a translatable data value corresponding to a separate base row of said Base Table.

7. The method of claim 6, wherein in the step of generating, in response to inserting said new language value, a set of sub-table rows, at least one of said rows, of said set of sub-table rows corresponding to said new language, is generated by copying a translatable data value of said default language.

8. The method of claim 7, wherein the step of generating, in response to inserting said new language value, a set of sub-table rows, further includes the step of:

marking invalid, said row generated by copying a translatable data value of said default language.

9. The method of claim 7, wherein in the step of generating, in response to inserting said new language value, a set of sub-table rows, at least one of said rows, of said set of sub-table rows corresponding to said new language, is generated by inserting a translatable data value of said new language value.

10. The method of claim 9 wherein the step of generating, in response to inserting said new language value, a set of sub-table rows, further includes the steps of:

marking valid, said row generated by inserting a translatable data value of said one language value.

11. The method of claim 9, further including the steps of:

deleting a base row from said Base Table; and

deleting, in response to deleting said base row from said Base Table, all sub-table rows corresponding to said base row.

12. The method of claim 11, wherein base rows of said Base Table include at least one untranslated value.

13. A machine-readable medium having stored thereon data representing sequences of instructions for storing and retrieving data values in a database management system (DBMS), said sequences of instruction which, when executed by a processor, cause said processor to perform the steps of:

generating a Base Table, said Base Table including at least one base row having a primary key, said base row exclusive of translatable data values;

generating a fully-populated Sub-Table, said Sub-Table includes a set of fully-populated sub-table rows corresponding to said base row, each said fully-populated row includes at least one translatable data field, said Sub-Table further includes a set of language values, wherein each set of sub-table rows in said SubTable corresponds to a separate language value of said set of language values;

selecting a language value from said set of language values as a default language;

inserting a second base row in said Base Table;

in response to inserting said second base row in said Base Table, generating a second set of sub-table rows in said Sub-Table, each row of said second set corresponding to a separate language value of said set of language values, wherein at least one of said rows in said second set is generated by copying a translatable data value of said default language.

14. The machine-readable medium of claim 13, wherein the step of generating a set of sub-table rows further includes the step of:

marking invalid, said row generated by copying a translatable data value of said default language.

15. The machine-readable medium of claim 13, wherein in the step of generating a set of sub-table rows, at least one of said sub-table rows is generated by inserting a translatable data value of a language value different from said default language.

16. The machine-readable medium of claim 15, wherein the step of generating a set of sub-table rows further includes the steps of:

marking valid, said row generated by inserting translatable data value of said language value different from said default language.

17. The machine-readable medium of claim 16, further including the steps of:

inserting a third base row in said Base Table, said third base row having a primary key;

generating, in response to inserting said third base row in said Base Table, a third set of sub-table rows in said Sub-Table corresponding to said third base row, said third set of sub-table rows including a row corresponding to each language value of said set of language values and at least one translatable data value.

18. The machine-readable medium of claim 17, further including the steps of:

inserting in said set of language values of said Sub-Table, a new language value;

generating, in response to inserting said new language value, a set of sub-table rows corresponding to said new language, wherein each row of said set includes a translatable data value corresponding to a separate base row of said Base Table.

19. The machine-readable medium of claim 18, wherein in the step of generating, in response to inserting said new language value, a set of sub-table rows, at least one of said rows, of said set of sub-table rows corresponding to said new language, is generated by copying a translatable data value of said default language.

20. The machine-readable medium of claim 19, wherein the step of generating, in response to inserting said new language value, a set of sub-table rows, further includes the step of:

marking invalid, said row generated by copying a translatable data value of said default language.

21. The machine-readable medium of claim 18, wherein in the step of generating, in response to inserting said new language value, a set of sub-table rows, at least one of said rows, of said set of sub-table rows corresponding to said new language, is generated by inserting translatable data value of said new language value.

22. The machine-readable medium of claim 21, wherein the step of generating, in response to inserting said new language value, a set of sub-table rows, further includes the step of:

marking valid, said row generated by inserting translatable data value of said one language value.

23. The machine-readable medium of claim 21, further including the steps of:

a) deleting a base row from said Base Table; and

b) deleting, in response to deleting said base row from said Base Table, all sub-table rows corresponding to said base row.

24. The machine-readable medium of claim 23, wherein said base rows of said Base Table include at least one untranslated value.

25. A computer system comprising:

a processor;

a computer readable medium coupled to said processor, said computer readable medium readable medium having stored thereon data representing sequences of instructions for storing and retrieving data values in a database management system (DBMS), said sequences of instruction which, when executed by said processor, cause said processor to perform the steps of;

generating a Base Table, said Base Table including at least one base row having a primary key, said base row exclusive of translatable data values;

generating a fully-populated Sub-Table, said Sub-Table includes a set of fully-populated sub-table rows corresponding to said base row, each said fully-populated row includes at least one translatable data field, said Sub-Table further includes a set of language values, wherein each set of sub-table rows in said SubTable corresponds to a separate language value of said set of language values;

selecting a language value from said set of language values as a default language;

inserting a second base row in said Base Table; and

in response to inserting said second base row in said Base Table, generating a second set of sub-table rows in said Sub-Table, each row of said second set corresponding to a separate language value of said set of language values, wherein at least one of said rows in said second set is generated by copying a translatable data value of said default language.

26. The computer system of claim 25, wherein the step of generating a set of sub-table rows further includes the step of:

marking invalid, said row generated by copying a translatable data value of said default language.

27. The computer system of claim 25, wherein in the step of generating a set of sub-table rows, at least one of said sub-table rows is generated by inserting a translatable data value of a language value different from said default language.

28. The computer system of claim 27, wherein the step of generating a set of sub-table rows further includes the step of:

marking valid, said row generated by inserting translatable data value of said language value different from said default language.

29. The computer system of claim 28, further including the steps of:

inserting a third base row in said Base Table, said third base row having a primary key;

generating, in response to inserting said third base row in said Base Table, a third set of sub-table rows in said Sub-Table corresponding to said third base row, said thirdset of sub-table rows including a row corresponding to each language value of said set of language values and at least one translatable data value.

30. The computer system of claim 29, further including the steps of:

inserting in said set of language values of said Sub-Table, a new language value;

generating, in response to inserting said new language value, a set of sub-table rows corresponding to said new language, wherein each row of said set includes a translatable data value corresponding to a separate base row of said Base Table.

31. The computer system of claim 30, wherein in the step of generating in response to inserting said new language value, a set of sub-table rows, at least one of said rows, of said set of sub-table rows corresponding to said new language, is generated by copying a translatable data value of said default language.

32. The computer system of claim 31, wherein the step of generating, in response to inserting said new language value, a set of sub-table rows, further includes the step of:

marking invalid, said row generated by copying a translatable data value of said default language.

33. The computer system of claim 31, wherein in the step of generating, in response to inserting said new language value, a set of sub-table rows, at least one of said rows, of said set of sub-table rows corresponding to said new language, is generated by inserting a translatable data value of said new language value.

34. The computer system of claim 33, wherein the step of generating, in response to inserting said new language value, a set of sub-table rows, further includes the step of:

marking valid, said row generated by inserting a translatable data value of said one language value.

35. The computer system of claim 34, further including the steps of:

deleting a base row from said Base Table; and

deleting, in response to deleting said base row from said Base Table, all sub-table rows corresponding to said base row.

36. The computer system of claim 35, wherein base rows of said Base Table include at least one untranslated value.

37. In a database management system (DBMS) having a Base Table and a Sub-Table, a method for retrieving a translatable data value the method comprising the steps of:

accessing the Base Table to obtain a non-translatable value, wherein the Base Table includes at least one base row having a non-translatable primary key and said base row is exclusive of translatable data values;

using the non-translatable value to retrieve a translatable data value from the Sub-Table, wherein the Sub-Table includes a set of fully-populated sub-table rows corresponding to the base row, each said fully-populated rows includes at least one translatable data field, said Sub-Table further includes a set of language values, wherein each sub-table row of said set of sub-table rows corresponds to a separate language value of said set of language values.

38. The method of claim 37, wherein the step of using the non-translatable value to retrieve a translatable data value from the Sub-Table further includes retrieving a fully-populated sub-table row from said Sub-Table, wherein a translatable data field of said row has stored therein a translatable data value of a defaulting language.

39. The method of claim 38, wherein said fully-populated sub-table row having said translatable data value of said defaulting language, is marked invalid.