Derive relationships based on data

[jex57]

Hi all,

I have a database - about databases - so what it does is stores all information about the various databases that we run, table names, columns that are linked column sizes, descriptions etc.

I have a System table that links to a MasterTable (table) and then a column table. The column table stores a foreign key and a primary key indicator.

Is there a way to derive relationships by reading through the MasterTable, picking up the Primary key indicator from the Column table, and then checking for foreign keys in other columns that belong to the same system.

I have code that can determine relationships between actual tables in MS Access, but I need to determine the relationships based on the data that is stored.

Thank you

 

[stumac]

If the foreign key and primary key have the same name you can use a self join on the table, see here:

http://allenbrowne.com/ser-06.html

 

[jex57]

If the foreign key and primary key have the same name you can use a self join on the table, see here:

http://allenbrowne.com/ser-06.html

This works if you are looking for table relationships. I am looking for a way to determine relationships from the date.

For example

SystemTable_data
SystemA
SystemB
SystemC

MasterTable_data
SystemA Table 1
SystemA Table 2
SystemA Table 3
SystemB Table 4


Column_data
Table 1 Column A PK
Table 1 Column A FK
Table 1 Column A
Table 1 Column A

Table 2 Column A PK
Table 2 Column A
Table 2 Column A
Table 2 Column A


Table_results - this is the table that I would need to generate
System Table RelationshipType
SystemA Table 1 Child to Table 2
SystemA Table 2 Parent to Table 1


Not sure if this makes sense