Database systems, methods and computer program products using type based selective foreign key association to represent multiple but exclusive relationships in relational databases

• US 20030046272A1
• Filed: 08/29/2001
• Published: 03/06/2003
• Est. Priority Date: 08/29/2001
• Status: Active Grant

• Current Assignee: Viavi Solutions, Inc. (Lumentum Holdings Inc.)
• Original Assignee: Trendium, Inc (Lumentum Holdings Inc.)
• Inventors: Yu, Lin, Yamany, Sameh, Aly, Alaaeldin A., Scanzano, Henry
• Granted Patent: US 7,693,845 B2
• Time in Patent Office: Days
• US Class Current: 707/2
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

Database systems, methods and/or computer program products provide for multiple but exclusive relationships between tables in a relational database. The multiple but exclusive relationship may be enforced based on a type associated with a record in a relating table. The multiple but exclusive relationship may be a one-to-many or a many-to-many relationship. A foreign key value of a record in a relating table may be selectively associated with a specific one of a plurality of related tables based on at least one attribute of the record containing the foreign key in the relating table. In particular embodiments of the present invention, the foreign key values of a record in the relating table are selectively associated with one of the plurality of related tables by defining a foreign key of records of the relating table and defining a plurality of types of foreign key associations, each of the types corresponding to a respective one of the plurality of related tables. One of the related tables having a type corresponding to a type value associated with a record of the relating table is selected and a record in the selected related table identified based on a foreign key value of the foreign key of the record in the relating table.

Claims

1. A method of providing multiple but exclusive relationships between tables in a relational database, comprising:

selectively associating a foreign key associated with a record in a relating table with a specific one of a plurality of related tables based on at least one attribute of the record in the relating table.

2. The method of claim 1, wherein the relating table comprises a first table and the related tables comprise a plurality of second tables, and wherein the step of selectively associating comprises the steps of:

defining a foreign key of records of the first table;

defining a plurality of types of foreign key associations, each of the types corresponding to a respective one of the plurality of second tables;

selecting one of the second tables having a type corresponding to a type value associated with a record of the first table; and

identifying a record in the selected second table based on a foreign key value of the foreign key of the record in the first table.

3. The method of claim 1, wherein the relating table comprises an entity table and the plurality of related tables comprise entity tables such that the foreign key and the at least one attribute provide a one-to-many relationship between the relating entity table and a corresponding one of the related entity tables.

4. The method of claim 3, wherein the step of selectively associating comprises defining a database trigger to enforce relationships between the types of foreign key associations and the corresponding particular one of the plurality of related tables.

5. The method of claim 3, wherein the step of selectively associating comprises the steps of:

defining a type associated with respective ones of the plurality of related tables in a type table;

accessing the type table to determine a type associated with a record in the relating table based on a value in the record in the relating table which identifies a record in the type table which identifies a type; and

associating the type identified in the type table with the record in the relating table so as to select one of the plurality of related tables which contain records having foreign key associations which point the record in the relating table.

6. The method of claim 1, wherein the relating table comprises a third table, the related tables comprise a plurality of second tables and wherein the third table relates records in a first entity table to records in a corresponding one of the second tables and wherein the step of selectively associating comprises the steps of:

defining a foreign key of records of the third table;

defining a plurality of types of foreign key associations, each of the types corresponding to a respective one of the plurality of second tables;

selecting one of the second tables having a type corresponding to a type value associated with a record of the third table; and

identifying a record in the selected second table based on a foreign key value of the foreign key of the record in the third table.

7. The method of claim 6, wherein the third table relates records in the first table to records in a corresponding one of the second tables such that the foreign key and type provide a many-to-many relationship between the second entity table and the first entity table utilizing the third relationship table.

8. The method of claim 7, wherein records in the third table have a type value which identifies one of the plurality of second tables and wherein the step of selecting one of the second tables having a type corresponding to a type value associated with a record of the third table comprises the steps of:

obtaining the type value of a record of the third table; and

selecting one of the second tables corresponding to the type value from the record of the third table.

9. The method of claim 8, wherein the step of obtaining the type value comprises joining the first table and the third table such that the type value associated with records in the first table is implicitly specified by the corresponding records in the third table.

10. The method of claim 7, wherein the step of defining a plurality of types of foreign key associations comprises defining a plurality of types of foreign key associations in a type table;

and

wherein the step of selecting one of the second tables comprises accessing the type table to determine a type of foreign key association associated with a record in the third table based on a value in the record in the third table which identifies a record in the type table which identifies a type of foreign key association.

11. The method of claim 8, wherein the step of defining a plurality of types of foreign key associations comprises defining a plurality of types of foreign key associations in a type table;

and

wherein the step of selecting one of the second tables corresponding to the type value from the record of the third table further comprises accessing the type table to determine a type of foreign key association associated with a record in the third table based on a value in the record in the third table which identifies a record in the type table which identifies a type of foreign key association.

12. A database system for providing multiple but exclusive relationships between tables in a relational database, comprising:

a relating table;

a plurality of related tables; and

means for selectively associating a foreign key value of a record in the relating table with a specific one of the plurality of related tables based on at least one attribute of the record containing the foreign key in the relating table.

13. The database system of claim 12, wherein the relating table comprises a first table and the related tables comprise a plurality of second tables, and wherein the means for selectively associating comprises:

means for defining a foreign key of records of the first table;

means for defining a plurality of types of foreign key associations, each of the types corresponding to a respective one of the plurality of second tables;

means for selecting one of the second tables having a type corresponding to a type value associated with a record of the first table; and

identifying a record in the selected second table based on a foreign key value of the foreign key of the record in the first table.

14. The database system of claim 12, wherein the relating table comprises an entity table and the plurality of related tables comprise entity tables such that the foreign key and the at least one attribute provide a one-to-many relationship between the relating entity table and a corresponding one of the related entity tables.

15. The database system of claim 14, wherein the means for selectively associating comprises means for defining a database trigger to enforce relationships between the types of foreign key associations and the corresponding particular one of the plurality of related tables.

16. The database system of claim 14, wherein the means for selectively associating comprises:

means for defining a type associated with respective ones of the plurality of related tables in a type table;

means for accessing the type table to determine a type associated with a record in the relating table based on a value in the record in the relating table which identifies a record in the type table which identifies a type; and

means for associating the type identified in the type table with the record in the relating table so as to select one of the plurality of related tables which contain records having foreign key associations which point the record in the relating table.

17. The database system of claim 12, wherein the relating table comprises a third table, the related tables comprise a plurality of second tables and wherein the third table relates records in a first entity table to records in a corresponding one of the second tables and wherein the means for selectively associating comprises:

means for defining a foreign key of records of the third table;

means for defining a plurality of types of foreign key associations, each of the types corresponding to a respective one of the plurality of second tables;

means for selecting one of the second tables having a type corresponding to a type value associated with a record of the third table; and

means for identifying a record in the selected second table based on a foreign key value of the foreign key of the record in the third table.

18. The database system of claim 17, wherein the third table relates records in the first table to records in a corresponding one of the second tables such that the foreign key and type provide a many-to-many relationship between the second entity table and the first entity table utilizing the third relationship table.

19. The database system of claim 18, wherein the means for selectively associating comprises means for defining a database trigger to enforce relationships between the types of foreign key associations and the corresponding particular one of the plurality of related tables.

20. The database system of claim 18, wherein records in the third table have a type value which identifies one of the plurality of second tables and wherein the means for selecting one of the second tables having a type corresponding to a type value associated with a record of the third table comprises:

means for obtaining the type value of a record of the third table; and

means for selecting one of the second tables corresponding to the type value from the record of the third table.

21. The database system of claim 20, wherein the means for obtaining the type value comprises means for joining the first table and the third table such that the type value associated with records in the first table is implicitly specified by the corresponding records in the third table.

22. The database system of claim 20, wherein the means for defining a plurality of types of foreign key associations comprises means for defining a plurality of types of foreign key associations in a type table;

and

wherein the means for selecting one of the second tables comprises means for accessing the type table to determine a type of foreign key association associated with a record in the third table based on a value in the record in the third table which identifies a record in the type table which identifies a type of foreign key association.

23. The database system of claim 20, wherein the means for defining a plurality of types of foreign key associations comprises means for defining a plurality of types of foreign key associations in a type table;

and

wherein the means for selecting one of the second tables corresponding to the type value from the record of the third table further comprises means for accessing the type table to determine a type of foreign key association associated with a record in the third table based on a value in the record in the third table which identifies a record in the type table which identifies a type of foreign key association.

24. A computer program product for providing multiple but exclusive relationships between tables in a relational database, comprising:

a computer readable medium having computer readable program code embodied therein, the computer readable program code comprising;

computer readable program code configured to selectively associate a foreign key associated with a record in a relating table with a specific one of a plurality of related tables based on at least one attribute of the record in the relating table.

25. The computer program product of claim 24, wherein the relating table comprises a first table and the related tables comprise a plurality of second tables, and wherein the computer readable program code configured to selectively associate comprises:

computer readable program code configured to define a foreign key of records of the first table;

computer readable program code configured to define a plurality of types of foreign key associations, each of the types corresponding to a respective one of the plurality of second tables;

computer readable program code configured to select one of the second tables having a type corresponding to a type value associated with a record of the first table; and

computer readable program code configured to identify a record in the selected second table based on a foreign key value of the foreign key of the record in the first table.

26. The computer program product of claim 24, wherein the relating table comprises an entity table and the plurality of related tables comprise entity tables such that the foreign key and the at least one attribute provide a one-to-many relationship between the relating entity table and a corresponding one of the related entity tables.

27. The computer program product of claim 26, wherein the computer readable program code configured to selectively associate comprises computer readable program code configured to define a database trigger to enforce relationships between the types of foreign key associations and the corresponding particular one of the plurality of related tables.

28. The computer program product of claim 26, wherein the computer readable program code configured to selectively associate comprises:

computer readable program code configured to define a type associated with respective ones of the plurality of related tables in a type table;

computer readable program code configured to access the type table to determine a type associated with a record in the relating table based on a value in the record in the relating table which identifies a record in the type table which identifies a type; and

computer readable program code configured to associate the type identified in the type table with the record in the relating table so as to select one of the plurality of related tables which contain records having foreign key associations which point the record in the relating table.

29. The computer program product of claim 24, wherein the relating table comprises a third table, the related tables comprise a plurality of second tables and wherein the third table relates records in a first entity table to records in a corresponding one of the second tables and wherein the computer readable program code configured to selectively associate comprises:

computer readable program code configured to define a foreign key of records of the third table;

computer readable program code configured to define a plurality of types of foreign key associations, each of the types corresponding to a respective one of the plurality of second tables;

computer readable program code configured to select one of the second tables having a type corresponding to a type value associated with a record of the third table; and

computer readable program code configured to identify a record in the selected second table based on a foreign key value of the foreign key of the record in the third table.

30. The computer program product of claim 29, wherein the third table relates records in the first table to records in a corresponding one of the second tables such that the foreign key and type provide a many-to-many relationship between the second entity table and the first entity table utilizing the third relationship table.

31. The computer program product of claim 30, wherein the computer readable program code configured to selectively associates comprises computer readable program code that defines a database trigger to enforce relationships between the types of foreign key associations and the corresponding particular one of the plurality of related tables.

32. The computer program product of claim 30, wherein records in the third table have a type value which identifies one of the plurality of second tables and wherein the computer readable program code configured to select one of the second tables having a type corresponding to a type value associated with a record of the third table comprises:

computer readable program code configured to obtain the type value of a record of the third table; and

computer readable program code configured to select one of the second tables corresponding to the type value from the record of the third table.

33. The computer program product of claim 32, wherein the computer readable program code configured to obtain the type value comprises computer readable program code configured to join the first table and the third table such that the type value associated with records in the first table is implicitly specified by the corresponding records in the third table.

34. The computer program product of claim 31, wherein the computer readable program code configured to define a plurality of types of foreign key associations comprises computer readable program code configured to define a plurality of types of foreign key associations in a type table;

and

wherein the computer readable program code configured to select one of the second tables comprises computer readable program code configured to access the type table to determine a type of foreign key association associated with a record in the third table based on a value in the record in the third table which identifies a record in the type table which identifies a type of foreign key association.

35. The computer program product of claim 32, wherein the computer readable program code configured to define a plurality of types of foreign key associations comprises computer readable program code configured to define a plurality of types of foreign key associations in a type table;

and

wherein the computer readable program code configured to select one of the second tables corresponding to the type value from the record of the third table further comprises computer readable program code configured to access the type table to determine a type of foreign key association associated with a record in the third table based on a value in the record in the third table which identifies a record in the type table which identifies a type of foreign key association.

36. A method of enforcing a multiple but exclusive relationship between a first table and a plurality of second tables, comprising:

associating a type with respective ones of the plurality of second tables;

associating a type with records in the first table such that the type of a record in the first table identifies which of the respective ones of the plurality of second tables the record is associated with; and

enforcing the multiple but exclusive relationship between records in the second tables and the first table based on the type associated with a record in the first table.

37. The method of claim 36, wherein the step of enforcing the multiple but exclusive relationship comprises the step of assuring that each record in each of the plurality of second tables has an associated foreign key which only points to records in the first table which have a type associated with the corresponding one of the plurality of second tables.

38. The method of claim 36, wherein the step of enforcing the relationship comprises the step of preventing entry of a record in one of the plurality of second tables which points to a record in the first table having a type other than a type associated with the one of the plurality of second tables.

39. The method of claim 36, wherein the type associated with the record in the first table is associated by providing a type table of types and accessing the type table based on attributes of the record in the first table so as to ascertain the type associated with the record.

40. A system for enforcing a multiple but exclusive relationship between a first table and a plurality of second tables, comprising:

means for associating a type with respective ones of the plurality of second tables;

means for associating a type with records in the first table such that the type of a record in the first table identifies which of the respective ones of the plurality of second tables the record is associated with; and

means for enforcing the multiple but exclusive relationship between records in the second tables and the first table based on the type associated with a record in the first table.

41. The system of claim 40, wherein the means for enforcing the multiple but exclusive relationship comprises means for assuring that each record in each of the plurality of second tables has an associated foreign key which only points to records in the first table which have a type associated with the corresponding one of the plurality of second tables.

42. The system of claim 40, wherein the means for enforcing the multiple but exclusive relationship comprises means for preventing entry of a record in one of the plurality of second tables which points to a record in the first table having a type other than a type associated with the one of the plurality of second tables.

43. The system of claim 40, wherein the means for enforcing the multiple but exclusive relationship comprises means for defining a database trigger which enforces the multiple but exclusive relationship.

44. The system of claim 40, wherein the type associated with the record in the first table is associated by providing a type table of types and accessing the type table based on attributes of the record in the first table so as to ascertain the type associated with the record.

45. A computer program product for enforcing a multiple but exclusive relationship between a first table and a plurality of second tables, comprising:

a computer readable storage medium having computer readable program code embodied therein, the computer readable program code comprising;

computer readable program code configured to associate a type with respective ones of the plurality of second tables;

computer readable program code configured to associate a type with records in the first table such that the type of a record in the first table identifies which of the respective ones of the plurality of second tables the record is associated with; and

computer readable program code configured to enforce the multiple but exclusive relationship between records in the second tables and the first table based on the type associated with a record in the first table.

46. The computer program product of claim 45, wherein the computer readable program code configured to enforce the multiple but exclusive relationship comprises computer readable program code configured to assure that each record in each of the plurality of second tables has an associated foreign key which only points to records in the first table which have a type associated with the corresponding one of the plurality of second tables.

47. The computer program product of claim 45, wherein the computer readable program code configured to enforce the multiple but exclusive relationship comprises computer readable program code configured to prevent entry of a record in one of the plurality of second tables which points to a record in the first table having a type other than a type associated with the one of the plurality of second tables.

48. The computer program product of claim 45, wherein the type associated with the record in the first table is associated by providing a type table of types and accessing the type table based on attributes of the record in the first table so as to ascertain the type associated with the record.